"Find all" and show the cell addresses in a MsgBox

[LuisE]

I need to perform a "Find" search in two sheets and then show the addresses
of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find
dialogbox)

I think I can create an array with the results and then show each element as
a line in the MsgBox, problem: I don't know how to do it....

Thanks in advance

 

[carlo]

Maybe this function helps you with what you are trying to do:
http://www.ozgrid.com/forum/showthread.php?t=27240

hth

Carlo

 

[Per Jessen]

I need to perform a "Find" search in two sheets and then show the addresses
of the matches in a MsgBox. I'm running XL2000 ( No Potions in the Find
dialogbox)

I think I can create an array with the results and then show each element as
a line in the MsgBox, problem: I don't know how to do it....

Thanks in advance

Sub Find()
SearchString = "Text" ' Fit to suit

i = 1
Sheets(1).Select
Range("A1").Select
Cells.Find(What:=SearchString, After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
Sh1String = ActiveCell.Address
FirstHit = ActiveCell.Address
Do
Cells.FindNext(After:=ActiveCell).Activate
Sh1String = Sh1String & ", " & ActiveCell.Address
i = i + 1
Loop Until FirstHit = ActiveCell.Address
Sh1String = Left(Sh1String, Len(Sh1String) - (Len(FirstHit) + 2))
MsgBox Sh1String
End Sub

Regards,
Per

 

[Jim Thomlinson]

Two things... Your solution will crash if no instance of the SearchString is
found and secondly don't use Find as the name of your sub. Find is a reserved
word an you might find that things can get messed up using that as the sub
name.

 

[Jim Thomlinson]

Something like this should do...

Sub FindAll()
Call FindStuff(Sheets("Sheet1"), "This") 'change to suit
Call FindStuff(Sheets("Sheet2"), "This")

End Sub

Sub FindStuff(ByVal wks As Worksheet, ByVal strToFind As String)
Dim rngFound As Range
Dim rngFoundAll As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set rngToSearch = wks.Range("A:A") 'change to suit
Set rngFound = rngToSearch.Find(What:=strToFind, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox strToFind & " could not be found in " & wks.Name
Else
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
MsgBox rngFoundAll.Address(External:=True)
End If
End Sub