Find Not Working in Formula Cell - VBA

[erock24]

I have code that loops through a range (a) and for each cell in that range it tries to find a match in another range (b). The code works great if range (b) cells are values only, but it doesn't work if they are formulas.

Here is the important part of the code:

For Each cl In baseRng
        If cl.Value <> "" Then
            Set rTableCol = Range(Cells(mstrRow, cl.Column), Cells(dailyRng.Rows.Count + 2, cl.Column))
            Set rFndValue = rTableCol.Find(What:=cl.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
            If Not rFndValue Is Nothing Then
******REST OF CODE******

I changed the "Lookin:=xlFormulas" to "Lookin:=xlValues" and that did not work. It won't run the "rest of code".

What gives??

-Eric

 

[Jerry Sullivan]

Hi Eric,

Your .find statement includes LookAt:=xlWhole
This means your String in cl.Value must match the entire Cell Contents (the whole formula). Is that what you intended?

If you want to find partial matches, use LookAt:=xlPart

 

[erock24]

Well, I think I want whole matches. Here is my problem:
if range(a) equals "23" and range(b) equals "23" then it works. But if range(b) equals "=23" then it doesn't work.

In other words, I want formulas in range(b), and those formulas should equate to exact matches in range(a). But when I do this my code doesn't work, it only works if I paste values on those formulas 1st. Hope I make sense.

 

[shg]

With the value 23 in B1 and the formula =23 in A4, this worked fine for me:

    Columns(1).Find(What:=[B1], LookIn:=xlValues, LookAt:=xlWhole).Select

 

[Jonmo1]

Post the formula in the range
rTableCol
I'm guessing that formula is returning a TEXT string, not a number..
Try adding +0 to the very end of that formula.

 

[erock24]

I tried adding +0 and that did not work. The only formula I've tried thus far in rTableCol is taking the value and adding an = sign infront of it.