Range find method and column width

Question

I have run into a problem that I haven't been able to find documentation on.

I have been populating a spread sheet with data. I was then searching my worksheet for a particular value and I found that some of them couldn't be found.

I "solved" my problem but I am very surprised about the behavior of Range.Find.

What happened is that when I populated the sheet some values were shown as ###### because the column size wasn't wide enough. I noticed that those were the values that couldn't be found. After I ran autofit on all columns I was able to find every value.

Is there something else I can do other than resize the columns?

EDIT UPDATE:

I first had this:

find.Find(day.ToShortDateString(), Type.Missing, Excel.XlFindLookIn.xlValues, 
Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, 
Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);

I switched it to:

find.Find(day.ToShortDateString(), Type.Missing, Excel.XlFindLookIn.xlFormulas, 
Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, 
Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);

and now the column width is not an issue.

In which cases would one be using the different .XlFindLookIn parameters?

Answer 1

Excel.XlFindLookIn.xlValues

Searches based on the value of the .Text property of the cells (I've seen some MS forum post where they say it's based on .Value2 but your case clearly proofs that wrong)

Excel.XlFindLookIn.xlFormulas

Searches based on the value of the .Formula property of the cells. This property contains the same value as the .Value2 property if the cell has a fixed value OR the actual formula used for that cell.

This all means that your own solution only works if your sheet contains only fixed values and won't find cells who's value comes from a formula.

Note that this search behavior is not VSTO specific. It is identical to how it works in the find dialog of Excel itself and also if you call if from VBA.

Easiest testcase:

• Enter 1234567 in cell A1
• Enter =A1 in cell A2
• Resize (shrink) the column widht till you get 1E+06
• Go to the search dialog (ctrl+f) and open the expand the options
• Enter 1234567 as the search value
• 'Find all' with look in 'Formula' will return "A1"
• 'Find all' with look in 'Values' will return nothing
• Now resize the column so the whole value is visible and start the find dialog again
• 'Find all' with look in 'Formula' will still return "A1"
• 'Find all' with look in 'Values' will now return both "A1" and "A2"