Searching

[Guest]

I use a macro that searches a spresdsheet for cells containing certain text.
I use the find like this:

..Cells.Find("GBP", LookIn:=xlValues)

the "GBP" is supposed to be unique in the spreadsheet in the sens that on
that worksheet only one cell has exactly that text. However other cells have
text like:
"Float GBP" or GBP(index)" and this causes problems becuase then the find
function does not always find the correct cell. I know it is possible to
change in the spreadsheet but I keep getting new info and it is not possible
to change it all manually. Is there any way of doing this, I mean search for
a unique cell content? I would be most grateful for any assistance. Thank you
very much!

 

[Norman Jones]

Hi Victor,

Try

'=============>>
Public Sub Tester()
Dim rng As Range
Const sStr As String = "GBP"

Set rng = ActiveSheet.UsedRange

Set rng = Cells.Find(What:=sStr, _
After:=rng(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
rng.Select
Else
MsgBox "Unable to find """ & sStr & """"
End If

End Sub
'<<=============