PDA

View Full Version : Use Find Across multiple workbooks/ multiple worksheet



Djblois
07-10-2008, 01:21 PM
I created a quick code to search all open workbooks and all the sheets in them for the user selected text. Here is the code I created:

Dim i As Long, j As Long
Dim wsTest As Worksheet


stgFind = Me.tbFind


Me.Hide

For i = 1 To Workbooks.Count

For j = 1 To Worksheets.Count
Set wsTest = ActiveSheet

wsTest.Cells.Find(What:=stgFind, After:=wsTest.Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Next j

Next i

DoEvents

Me.Show

It keeps telling me object variable or With block not set on the find line. I know it is probably something so small that I can't find. I also set stgFind as a public variable.

grichey
07-10-2008, 02:24 PM
pardon but what does ME. do? Is that supposed to be a vba keyword or just some std example term? Believe it or not doing a google search for vba me gives more a few matches...

Djblois
07-11-2008, 05:42 AM
me. do means that code is in a form and it refers to itself

Djblois
07-11-2008, 07:09 AM
This works Great now:

Private Sub CommandButton4_Click()

Dim lCount As Long
Dim lOccur As Long
Dim rCell As Range

Application.ScreenUpdating = False

stgFind = Me.tbFind 'tbfind is a textbox
Me.lbFound.Clear 'lbFound is a listbox

On Error Resume Next
For j = j To Workbooks.Count
Workbooks(j).Activate

If i > Worksheets.Count Then
i = 1
GoTo nextWorkbook
End If
For i = i To Worksheets.Count

Worksheets(i).Activate
Set rCell = ActiveSheet.Cells(1, 1)
'Pass the number of times strfind1 occurs
lOccur = WorksheetFunction.CountIf(Range("A:C"), stgFind & "*")

For lCount = 1 To lOccur
Set rCell = ActiveSheet.Range("A:C").Find(What:=stgFind, After:=rCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

Me.lbFound.AddItem rCell.Row
Next lCount

If i = Worksheets.Count And lOccur = 0 Then Exit For
If lCount > 1 Then
i = i + 1
GoTo endSub
End If
Next i
nextWorkbook:
Next j

endSub:

Application.ScreenUpdating = True


End Sub

Except for one problem. It will find the string in the worksheets in the first workbook and display the rows correctly and then when it gets to the next workbook if finds an occurrence of the string but doesn't display the correct row number.

mdmackillop
07-12-2008, 03:38 PM
A hyperlink version here (http://vbaexpress.com/kb/getarticle.php?kb_id=780) for one workbook