Use VBA to search displayed text in cell, not formula

[CallMeDave]

Hello all.
I don't know the precise technical definitions for these concepts so please bear with me!

I wrote a database that pre-fills various cells on other tabs depending on data entered in the main tab.
e.g.
If a user enters PP4 -2 in a cell on the main tab, a specific cell in another tab will have this formula inserted:
=IF(X16="[Procedure?]", "", IF(X16="", "", IF(X16="Yes", "PP4 - 2", IF(X16="No", "PP4 - 2", ""))))
This cell will appear blank until the user fills in neighbouring cells. Then this cell will autofill with "PP4 - 2" (the alphanumeric selected on the main tab when they created the corresponding entry).
The cell also contains a drop down menu. The user can use this to override the autofill and replace it with other alphanumerics from the menu.
99% of the time the autofill text is applicable and the user will not change it. In specific circumstances, the user will use the dropdown to change it.
All good so far.

I wrote a script to extract and collate all this data.
It searches using this: Set rngFound = Rng.Find(search_value, LookAt:=xlWhole, SearchOrder:=xlByRows)
This works for everything apart from the cells that are autofilled. In the autofilled cells it finds the formula in the cell, rather than the displayed text.
e.g.
Cell Y15 and cell Y16 look identical. They both show PP4 - 2 to the human eye.
Cell Y15 was manually filled by the dropdown so when I click into the cell the display bar at the top shows PP4 - 2.
Cell Y16 was autofilled with the formula so when I click into the cell the display bar at the top shows =IF(X16="[Procedure?]", "", IF(X16="", "", IF(X16="Yes", "PP4 - 2", IF(X16="No", "PP4 - 2", ""))))

When I run various scripts to extract the data the search function I am using finds the PP4 - 2 in the manually filled cell but fails to recognise the PP4 -2 in the autofilled cell.

However when I run something like Set TEST_value = ThisWorkbook.Sheets("Schedule1").Cells(16, 25) on the autofilled cell, TEST_value successfully returns PP4 - 2, rather then the formula.
So, VBA can definitely 'see' the displayed text but the search function I am using cannot.

Is there a different search function or some kind of extra parameter I can use to force it to look at the text the cell is displaying rather then the formula in the cell?

I hope that makes sense!

Thanks,
Cameron

 

[Artik]

In the Find method, try additionally using the LookIn:= xlValues parameter.
It is recommended that you set all parameters of the Find method because these settings are “remembered” between macro calls. And we are never sure how the parameters are set immediately before running the macro.

Artik

 

[CallMeDave]

Amazing. That's done it.
Of course now you've given me something to search I can immediately see lots of pages that make this solution obvious!