searching when extra spaces exists in value

[abenitez77]

I am using vba to search for searching for certain key words/phrases in excel spreadsheets. Sometimes a phrase may have extra spaces in between words
i.e. "Forecast O&A $ per Unit"

When I search i use "Forecast O&A $ per Unit" (only one space between each word). In my spreadsheet "Forecast", "Unit" and other phrases may appear in other cells with other words. How can i search what I am looking for and get a hit even when there are extra spaces in value ?

 

[Rick Rothstein]

I am using vba to search for searching for certain key words/phrases in excel spreadsheets. Sometimes a phrase may have extra spaces in between words
i.e. "Forecast O&A $ per Unit"

When I search i use "Forecast O&A $ per Unit" (only one space between each word). In my spreadsheet "Forecast", "Unit" and other phrases may appear in other cells with other words. How can i search what I am looking for and get a hit even when there are extra spaces in value ?

How are you doing the search? It might be helpful to see your code. When posting your code, put [/code] after the last line and put

[/COLOR][/B] before the first line.

 

[abenitez77]

How are you doing the search? It might be helpful to see your code. When posting your code, put [/code] after the last line and put

[/COLOR][/B] before the first line.[/QUOTE]


[code]


Set FoundCell = SearchRange.Find(what:=FindWhat, _
            after:=LastCell, _
            LookIn:=LookIn, _
            LookAt:=XLookAt, _
            SearchOrder:=SearchOrder, _
            MatchCase:=MatchCase)

 

[Rick Rothstein]

Set FoundCell = SearchRange.Find(what:=FindWhat, _
            after:=LastCell, _
            LookIn:=LookIn, _
            LookAt:=XLookAt, _
            SearchOrder:=SearchOrder, _
            MatchCase:=MatchCase)

I was afraid you did it that way (as opposed to doing a cell by cell comparison). Whether this solution will work 100% of the time depends on what possible values can be in SearchRange. As long as no other value in SearchRange can have all of the individual words "Forecast", "O&A", "$
, "per" and "Unit", in that exact order with or without other text between them, then replacing FindWhat with Replace(FindWhat, " ", "*") should work.

 

[abenitez77]

I was afraid you did it that way (as opposed to doing a cell by cell comparison). Whether this solution will work 100% of the time depends on what possible values can be in SearchRange. As long as no other value in SearchRange can have all of the individual words "Forecast", "O&A", "$
, "per" and "Unit", in that exact order with or without other text between them, then replacing FindWhat with Replace(FindWhat, " ", "*") should work.

Thanks!, looks like it should work. I will give this a shot.