Please explain difference between Find --> Look In --> Values vs Formulas

[SLARRA]

I have searched Microsoft's Help, this forum, and other forums, but I cannot turn up a comprehensive explanation of the differences between the Values vs Formulas options for the "Look In" parameter of Excel's Find dialogue box and its VBA cousin the Range.Find expression. Both offer the option to "Look In" either Formulas or Values; however, I'm not sure I understand the implications of these options. I would be grateful if someone can point me to a good explanation of the differences and limitations. In addition, I have some specific questions:

Does "Look In" Formulas really mean "look at the formula construct itself (i.e. what appears in the Formula Bar) or, if a cell is populated with a constant (i.e. not a formula result), then look at that value"? In other words, does the "Look In" Formulas limit the examination to ONLY those cells in the range that have formulas, or does it still examine every populated cell in the range?

And, when using "Look In" Values, what limitations should I be aware of? Shockingly, I have learned that it is sensitive to the displayed value, as a too narrow column that results in the value being displayed as "###" won't return a hit with this Find method, but the expanded column that shows the value in its entirety will return a hit. Are there other limitations that might trip-up the average Excel user?

Lastly, any specific advice when applying VBA Find to a spreadsheet that is opened from a csv text file, using OpenText method that defines one field as a date with the DMY format? I found that LookIn:=xlValues won't return a hit on a date value; however, LookIn:=xlFormulas will. Does the import/definition process essentially render those date values as the result of a formula (e.g. =DATEVALUE("01-04-13") or =TEXT("01-04-13","DD-MM-YY"), both crazy I'll admit), even tho they appear as constants in the resultant cells?

Thanks in advance,

SDL

 

[JoeMo]

Sorry I don't have a good reference for you, but I will attempt to provide some information relevant to your specific questions about the Find Method.

Does "Look In" Formulas really mean "look at the formula construct itself (i.e. what appears in the Formula Bar) or, if a cell is populated with a constant (i.e. not a formula result), then look at that value"? In other words, does the "Look In" Formulas limit the examination to ONLY those cells in the range that have formulas, or does it still examine every populated cell in the range?

Yes lookin:=xlformulas will look at the formulas in formulaic cells within the specified find range, but it is not limited to formulas. In A1 type the string "SUM" in A2:A5 enter some numbers and in A6 enter =SUM(A2:A5). Then run this macro:

Sub testFindArguments()
Dim fWhat As String, R As Range
fWhat = "SUM"
Set R = ActiveSheet.UsedRange.Find(what:=fWhat, after:=[A1], LookIn:=xlValues, _
    lookat:=xlPart, searchdirection:=xlNext, searchformat:=False)
If R Is Nothing Then
    MsgBox "not found using xlValues"
Else
    MsgBox "found using xlValues in cell " & R.Address
End If
Set R = ActiveSheet.UsedRange.Find(what:=fWhat, after:=[A1], LookIn:=xlFormulas, _
    lookat:=xlPart, searchdirection:=xlNext, searchformat:=False)
If R Is Nothing Then
    MsgBox "not found using xlFormulas"
Else
    MsgBox "found using xlFormulas in " & R.Address & " for which HasFormula = " & R.HasFormula
End If
End Sub

Notice that the string "SUM" is found in A1 using xlValues (and lookat:=xlPart) and in A6 using xlFormulas (and lookat:=xlPart). Now delete the formula in A6 and run the macro again. Now both xlValues and xlFormulas find the first occurrence of "SUM" in A1. The take away is that xlFormulas can look at both formula content and values whereas xlValues is limited to values. I use xlFormulas almost exclusively unless I suspect that a constant value I want to find might be part of a formula construct.

And, when using "Look In" Values, what limitations should I be aware of? Shockingly, I have learned that it is sensitive to the displayed value, as a too narrow column that results in the value being displayed as "###" won't return a hit with this Find method, but the expanded column that shows the value in its entirety will return a hit. Are there other limitations that might trip-up the average Excel user?

If you change to lookin:=xlFormulas to find say a number that is in a too-narrow column (i.e. shows ###), you will find it.

Lastly, any specific advice when applying VBA Find to a spreadsheet that is opened from a csv text file, using OpenText method that defines one field as a date with the DMY format? I found that LookIn:=xlValues won't return a hit on a date value; however, LookIn:=xlFormulas will. Does the import/definition process essentially render those date values as the result of a formula (e.g. =DATEVALUE("01-04-13") or =TEXT("01-04-13","DD-MM-YY"), both crazy I'll admit), even tho they appear as constants in the resultant cells?

I would stick with xlFormulas when searching for date values.

 

[MARK858]

No technical knowledge here but I only differentiate between them when finding the last row / column.
Values ignores empty string results from formulas, Formulas recognises them.

 

[SLARRA]

Thank you to both Mark858 and JoeMo for their insights and good advice!

Tho I would still appreciate any links to a fuller dissertation on these parameters and their limitations, my fundamental questions have been answered, and I'll consider this post resolved.

SDL

 

[AlphaFrog]

This is how I understand it for what it's worth.

First thing to understand is that the .Find method is a text comparison... always. It's only looking for text-string matches. This is important. It can match numbers and dates, but it's based on text-comparisons of those values. e.g.; January 1, 2014 does not match 1/1/2014.

The best way to understand what strings are searched within a range is to understand the four different properties that comprise a cell's contents.

.Formula
.Value
.Value2
.Text

These four cell properties can return the same or different results depending on the contents of the cell and the cell's formatting.

A cell's .Formula property is what's seen in the formula bar. It can be an actual formula or it can be a constant. The LookIn:=xlFormulas argument searches the .Formula property of cells. The formula property is not affected by cell formatting. So if the cell contains a date, the .Formula property is the date with the default date format (usually m/d/yyyy for US, or d/m/yyyy for many others) as seen in the formula bar.

The .Value property is a formula-result or a constant. The .Find method doesn't have an argument to search the .Value property for a range of cells. The LookIn:=xlValues argument does not look at a cell's .Value property. It looks at the .Text property (below).

The .Value2 property is just like the .Value property except if the cell's value is a date or currency. The .Value2 property returns as a "double" data-type for those values. The .Find method doesn't have an argument to search the .Value2 property of a range of cells.

A cell's .Text property is what's displayed on the worksheet. The LookIn:=xlValues argument looks at the .Text property of cells. The .Text property is affected by cell formatting. So if the cell contains a date constant, the .Text property is the date as displayed in the cell. If you use .Find for dates using the LookIn:=xlValues argument, the What:=date formatting has to match the cell's display date formatting.


Notes:

The .Find method ignores hidden and filtered cells.

Most common problem I see is matching dates. It's usually due to date format differences between the What:= and LookIn:= args. and not understanding that it's a text comparison.

I hope that helped some.

 

[SLARRA]

AlphaFrog - that is an outstanding explanation! Coupled with the advice from JoeMo and Mark858, I think I finally understand those parameters for the Find expression. I am so grateful for the MrExcel forum and you gurus because this info simply isn't readily available in the Microsoft Help. Thank you!

SDL

 

[mattstoc]

I agree 100% this is a great thread explaining the differences. I had a strange situation that really had me scratching my head. Basically, after I ran a macro (that has a find embedded), my native excel find would no longer work. When I closed and re-opened the sheet the find worked once again. It turned out to be a combination of the macro .find was using lookin xlValues. The native excel value I was looking for had been formatted as Accounting. That combination was the problem. CRAZY man

 

[AlphaFrog]

I agree 100% this is a great thread explaining the differences. I had a strange situation that really had me scratching my head. Basically, after I ran a macro (that has a find embedded), my native excel find would no longer work. When I closed and re-opened the sheet the find worked once again. It turned out to be a combination of the macro .find was using lookin xlValues. The native excel value I was looking for had been formatted as Accounting. That combination was the problem. CRAZY man

That sounds correct. The .Find method has default settings when you start an instance of Excel. If you then use it and change a setting (either manually or with a macro), then it retains the previously used settings for the next time you use it. So your macro was changing the settings from the default xlFormulas to xlValues. When you later used it manually again, it retained the xlValues setting.

Glad you figured it out.