Does XlFindLookIn.xlFormulas include XlFindLookIn.xlValues

[David Thielen]

Using Range.Find() in Excel it appears that using
XlFindLookIn.xlFormulas will return all formulas and cells with just a
value. Does this hold for all cases, that XlFindLookIn.xlFormulas
includes formulas plus what XlFindLookIn.xlValues will find?

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Colbert Zhou [MSFT]]

Hi Dave,

The xlFormulas means that Excel will try to find and match according to the
cell.Formula property. The xlValues means that Excel will find and match
according to the cell.Value2 property.

For example, in my side, I do the following test,
CellAddress Value
A1 1
A2 2
A3 =SUM(A1,A2)

In this case, we can observe these three cells in the debugger and we can
find,
Sheet1.Cells(1,1).Value = 1
Sheet1.Cells(1,1).Formula =1

Sheet1.Cells(2,1).Value = 2
Sheet1.Cells(2,1).Formula =2

Sheet1.Cells(3,1).Value = 3
Sheet1.Cells(3,1).Formula ="=SUM(A1,A2)"

So if we use xlForumlas to find "1", it will return A1 and A3 because both
of their Formula property includes "1". If we use xlFormlas to find "3", it
returns nothing although A3's value is 3.


Best regards,
Colbert Zhou
Microsoft Online Support Team

 

[David Thielen]

Hi Dave,

The xlFormulas means that Excel will try to find and match according to the
cell.Formula property. The xlValues means that Excel will find and match
according to the cell.Value2 property.

For example, in my side, I do the following test,
CellAddress Value
A1 1
A2 2
A3 =SUM(A1,A2)

In this case, we can observe these three cells in the debugger and we can
find,
Sheet1.Cells(1,1).Value = 1
Sheet1.Cells(1,1).Formula =1

Sheet1.Cells(2,1).Value = 2
Sheet1.Cells(2,1).Formula =2

Sheet1.Cells(3,1).Value = 3
Sheet1.Cells(3,1).Formula ="=SUM(A1,A2)"

So if we use xlForumlas to find "1", it will return A1 and A3 because both
of their Formula property includes "1". If we use xlFormlas to find "3", it
returns nothing although A3's value is 3.

Ok thanks. When I tested it the formula was putting the same text in
the value and that's why both worked. Glad I asked.

thanks - dave


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm