searching for strings

[Luis939]

I have my statements which will search a range's in another workbooks

myrange="mystring"
set mystring = workbooks("myworkbook&quot.activesheet.usedrange.find(mystring)

however is there anyway so that if the value that im searching for contains blanks (for example "my string&quot, then the program can ignore those blanks, perhaps by a function, and correctly match the string, thanks!

 

[ETID]

myrange = application.WorksheetFunction.Substitute("mystring"," ","&quot

 

[Luis939]

Well thats not exactly what I'm looking for, but thats becuase i wasnt too entirely clear on what i needed help on. I know that the string I have will NOT have blanks, however, when I implement the Find function i want the function to look in the worksheet for all strings (disregarding all blanks) so that if say find "mystring" that it will still be able to match that with "my string" in the worksheet that i am searching for. I'm not even sure if that can be done but thanks!!

 

[ETID]

cells(1,1).select
Cells.Find(What:="mystring", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

 

[Luis939]

Well no that cant work because lets say i use that function but in my workbook there are only cells that have "my string" then it wont find it because that empty character " " between my and string makes that completely different. I just wanted to be able to use that or another similar function so that if i search for "mystring" that it will select any cells that have "my string". I just want to look for the strings that have any blanks in them ignored, i though i read a function that doest that but i dont know?

 

[ETID]

Sorry...no magic here :-(

 

[Luis939]

thats cool thanks anyway

 

[ETID]

....you could write a piece of code that loops through each cell in your workbook, then branches to a sub that compares that value to all combinations of "mystring" and if a match is found, highlite that cell and move to the next.


If your .xls is very large, this can be a slow process.

 

[Luis939]

Yea i could, but it is a big file and the string i'm trying to find is within other strings, so its gonna be hard but thanks for the ideas!!!