Erratic FIND results

[ChuckDrago]

Hi everyone,

This problem is driving us all nuts! I have a table with 4 columns. A macro seeks to FIND a given number, looping through column A, and when found report the values in cols B,C, and D via ActiveCell.Offsets.

Cell (A11) contains the value 110. However, the results of the offset figures do not match expected results. In inspecting the table, I noticed that the value on cell (C5) is $1,105,899 and the reported offsets correspond to its neighboring figures. It appears that the 110 start of C5 has tricked the FIND method to deliver the wrong result.

The find syntax I am using is:

Columns("A:A").Select
Cells.Find(What:=Fam, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

where Fam is the variable holding the values 101, 102, etc. in the search loop.

Any clever ideas to force the search to column A and only column A?

 

[p45cal]

Change
Cells.Find
to
Range("A:A").find

 

[VoG]

Try

Columns("A").Find(What:=Fam, After:=ActiveCell, LookIn:=xlFormulas, lookat _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False, lookat:=xlWhole).Activate

[/code]

 

[Jonmo1]

2 things...

1st. Just because you selected column A, that doesn't restrict Cells.Find to column A.
It's still using Cells (which means ALL cells)..

don't bother selecting...
Just do Columns("A:A").Find(....

That will restrict it to only column A.


2nd. it's the LookAt:=xlPart that made it think $1,105,899 = 110
Change that to xlWhole

xlPart = close match (similar to the Find and Search Functions)
xlWhole = exact match


Hope that helps.

 

[ChuckDrago]

A round of robust thanking to all of you guys... This place is the Oracle of Delphi, no database pun intended...

jonmo1's comments: crystal clear, and a lesson in themselves.

VoG; your solution of adding xlWhole was the ticket, as long as you remove xlPart (it reported errors with the two in the same syntax).

pcal45: above solution worked, but thanks a bunch anyway.

Chuck