Type mismatch using Find method

[BoBeau]

Hi
I'm using Excel2003. I have a number of cells in a sheet with a formula similar to "=IF((RC[-57]+RC[-38])>20;IF(RC[-19]>=0,07;"XX";"");"")"

I need my VBA code to find these cells and perform some action. If I use code like

Set b = Cells.Find("XX")

I can find the cells that have the VALUE "XX", but I need to find all the cells with the formula. My problem is that if I use instead

Set b = Cells.Find("XX", xlFormulas)

I get a "Runtime error 13 - Type Mismatch".
Any advice or ideas are much appreciated.
Thx

 

[jmthompson]

How is b "dimmed"?

 

[MrKowz]

Welcome to the forums!

Try:

Set b = Cells.Find("XX", LookIn:=xlFormulas)

The second argument to the Find method is "after", so without explicitly declaring LookIn:=, the method is trying to say "After:=xlFormulas", which is msimatched.

 

[BoBeau]

Just tried the "Lookin" suggestion and that did the trick. Thought the "xlFormulas" was reserved enough to be understood, but apparantly not.
Well spotted and thanks a lot for your help.
Regards

 

[MrKowz]

Just tried the "Lookin" suggestion and that did the trick. Thought the "xlFormulas" was reserved enough to be understood, but apparantly not.
Well spotted and thanks a lot for your help.
Regards

Anything in VBA that is "xl*something*" or "vb*something*", like "xlFormulas", "xlUp", "xlToLeft", etc, are all constants that are set so we don't have to remember things like -4123 for xlFormulas, -4162 for xlUp, or -4159 for xlToLeft.

 

[BoBeau]

Gotya )