Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for text "beginning with" "containing" wildcards | Excel Worksheet Functions | |||
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe") add more like "ELB" "FLG" | Excel Worksheet Functions | |||
=IF(ISERROR(SEARCH("insurance",A125,1)),"","*") | Excel Worksheet Functions | |||
Problem with "Search box" in Help window | Excel Discussion (Misc queries) |