"Search" Macro problem Help!!!!
 

Hi everyone,

I have this macro that searches for the value in column A in every
sheets and return the name of the sheet if the value is found in
column C

Major problem I am having with this macro is that when it searches it
doesn't go for the exact value but everything that includes the search
value.
i.e. if I run a search for "123" and if there is a entry such as
"ABC123" in sheet2 It will count that as search result.

How do I alther my code in order to search for exact values?

Thank you for your help in advance.

regards,


James



Sub SearchAllSheets()

For i = 6 To 116
If Range("A" & i) < "" Then
Range("C" & i).ClearContents
For Each sh In Worksheets
If sh.Name < ActiveSheet.Name Then
Set searchresult = sh.Cells.Find(Range("A" & i), _
lookat:=xlPart)
If Not searchresult Is Nothing Then
Range("C" & i) = Range("C" & i) & " " & " | " & sh.Name
End If
End If
Next
End If
Next


End Sub

"Search" Macro problem Help!!!!
 

Try changing lookat:=xlPart to LookAt:= xlWhole
--
Regards,

OssieMac


"James8309" wrote:

Hi everyone,

I have this macro that searches for the value in column A in every
sheets and return the name of the sheet if the value is found in
column C

Major problem I am having with this macro is that when it searches it
doesn't go for the exact value but everything that includes the search
value.
i.e. if I run a search for "123" and if there is a entry such as
"ABC123" in sheet2 It will count that as search result.

How do I alther my code in order to search for exact values?

Thank you for your help in advance.

regards,


James



Sub SearchAllSheets()

For i = 6 To 116
If Range("A" & i) < "" Then
Range("C" & i).ClearContents
For Each sh In Worksheets
If sh.Name < ActiveSheet.Name Then
Set searchresult = sh.Cells.Find(Range("A" & i), _
lookat:=xlPart)
If Not searchresult Is Nothing Then
Range("C" & i) = Range("C" & i) & " " & " | " & sh.Name
End If
End If
Next
End If
Next


End Sub