using search function in macro

T

tam

cells(1, 1).Formula = "=Search("":"",Sheet2!cells(1,1),1)"
What is wrong with this formula? How do I use this type of cell reference
in a formula?
 
D

Don Guillett

Look in vba help for using functions in macros or just use the FIND method
in vba instead. Also in HELP.
 
T

tam

Thanks, David. However, when I used this formula, I got "#NAME?". It looks
like the formula doesn't like the type of cell reference Cells(1,1). If I
changed cells(1,1) to A1, then everything worked fine. However, I have a
long list and I need to do a loop later on.
Regards,
Minh
 
D

David Hepner

This should work for you:

Worksheets("Sheet1").Cells(1, 1).Formula =
"=Search("":"",Sheet2!cells(1,1),1)"
 
D

Dave Peterson

Depending on what your loop looks like...

Maybe you could do it like this instead of the loop:

Range("a1:A99").formula = "=Search("":"",Sheet2!A1,1)"
 
Top