Find Lookat:=xlWhole

[Gives]

Does xlWhole look at more that a cell's value when used in a Find statement?

When I use the atached statement in the immediate window it returns 54 (see cut and paste)

? ThisWorkbook.Sheets("Database").rows(1).Find(what:=ThisWorkbook.Sheets("Database").cells(1,54), lookat:=xlpart).column
54

when I change xlpart to xlwhole I get an error:

Run-time error '91':
Object variable or With block not set

How can xlwhole give an error when it's looking for the cell?

The Cell contents are:
Annual MM BTU Benefit

I'm stumped! Any help appreciated.

 

[Von Pookie]

xlPart = looks at the text in the cell for any match.
If you search for any part of the text (such as only the word 'annual' or 'benefit'), it will return that cell in row 54.

xlWhole = looks at the entire/exact entry in the cell to see if it matches.
Unless you are searching for "Annual MM BTU Benefit," it will *not* return that cell in row 54.

 

[mikerickson]

Have you tried specifying the After argument?

You also might try changing to
what:=ThisWorkbook.Sheets("Database").cells(1,54).Value

 

[Gives]

Von Pookie - thanks for the quick reply but if you'll notice I'm looking for cells(1,54) on row 54. How can it not = xlwhole?

 

[Gives]

mikerickson-tried adding .value and after:=thisworkbook.sheets("Database").cells(1,1) with the same results - works for xlPart but not xlWhole

 

[Von Pookie]

Your code seems to work for me using both xlPart and xlWhole. Not sure what's going on, there.

Just wondering, though: if you already know that the value you want to look for is in column 54--as is noted by your searching for the specific value of cells(1,54)--why are you searching to return the same column number?

 

[RoryA]

Did you definitely have the right workbook active when you executed the code?

 

[Gives]

Von Pookie - my actual code has a text string rather than the cell contents. I tested the cell contents when I got the error with my original code to see if there an erro in the text string. That's when I discovered this glitch. As a temporary solution I am chnaging from xlWhole to xl Part but if anyone comes across a solution I sure would like to hear about it.

 

[RoryA]

Actually, ignore my last post as it's irrelevant. Did you run any other find operations in between? Could it be that the code is looking in the cell's formula rather than its value, or that it is now using a case sensitive find? (It is always safer with a Find operation to specify all the options to be sure they are set to what you expect and to check that there's a found range before attempting to work with it)

 

[Allan George]

I recently had the same issue.

The problem was the format of the cell. Once I manually changed the cell's format to text .Find() worked as expected.