Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 14th, 2007, 10:29 AM
Registered User
Join Date: Jun 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Searching Multiple Sheets for Data?

I'm having some trouble with the code below, I basically need to search mulitple sheets for a set value and then be able to tag the data once it's found. I eventually will need copy the entire column into a new sheet, but for now being able to find it and select it works. One problem is that the number of sheet tabs will vary, so I can't count on a set number of sheets to search. The code below seems to work, until the second instance of the error occurs, then it dies.

Any help would be appreciated.

Public Sub test()

    Dim i As Integer
    Dim f As String
    Dim x As Long

    x = ActiveWorkbook.Sheets.Count
    i = x

    f = "FindMe"


    On Error GoTo FindMissingData

    If i = 0 Then
        MsgBox "Done"

        Cells.Find(What:=f, MatchCase:=True).Activate
        Selection.Font.Bold = True

    End If

    i = i - 1

    GoTo Sheet_Loop

    Exit Sub

    i = i - 1
    GoTo Sheet_Loop

End Sub

Old June 14th, 2007, 11:25 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts

Th error should occur if you cannot find the required range

Hence just change the part of your find code to

        Set rng = Cells.Find(What:=f, MatchCase:=True)
        If Not rng Is Nothing Then
            Selection.Font.Bold = True
        End If

this should work


Old June 20th, 2007, 09:14 AM
Registered User
Join Date: Jun 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks! That worked.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching Multiple Columns in Acess JezLisle Access 1 April 24th, 2008 11:37 AM
Problem in searching multiple keywords kumiko Classic ASP Basics 2 April 3rd, 2008 08:12 PM
Searching multiple tables melkin Classic ASP Databases 0 March 10th, 2008 11:48 AM
Export data to several excell sheets at will milocold Crystal Reports 0 November 16th, 2005 11:42 AM
Loading from multiple sheets on another workbook luxcs Excel VBA 0 March 25th, 2004 03:00 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.