Excel not searching right

J

johnfli

I have no idea what the deal is. I have used this macro for months and it
has always worked. But now its not.

Ok, here is how it goes.
The macro looks on one worksheet and gets a value and puts that value into
'wrdgpro'
Tha part works.
Then it goes to the other worksheet (summit) and looks for that value in
column 'a'
I have stepped through it zillions of times. And it is not finding the
matching values, 'C' always equals nothing.
ANd yes, I have even made sure that there are matching values.

Any ideas??



Sheets("summit").Select
With Worksheets(1).Range("a1:a3000")
Set C = .Find(wrdgpro, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
C.EntireRow.Delete



Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
 
F

Frank Kabel

Hi
try replacing
Sheets("summit").Select
With Worksheets(1).Range("a1:a3000")

with the statement
With Worksheets("summit").Range("a1:a3000")
 
D

Dick Kusleika

johnfli

Two possibilities that I can see. Find will use your settings from the last
find you did, whether in VBA or the UI. So, if you chose "match whole cell"
the last time you did a find, but don't specify it in your code, it will
still be using that setting. Make sure you specify all the arguments for
Find in VBA to be safe. See also here
http://www.dicks-blog.com/excel/2004/03/the_find_method.html

If that's not it, you may have non-printable characters in column A. How
have you made sure there are matching values? Find a cell that you know
matches and put a line like

Debug.Print Cell("A15").Value = wrdgpro

and see if it's True. If not, then you may have stuff that you can't see in
those cells. Check out the TRIM and CLEAN worksheet functions to clean up
that data. This is particularly likely if the data was imported.

One final thought. You don't issue a FindNext inside your loop. The way
it's written it will never execute the loop more than once. I'm not sure
what happens to C when you delete the row, but it may turn to Nothing which
would also be bad. If you find that's the case, you should Find in reverse
order using the SearchDirection argument or save the found cell to another
variable, do a FindNext, then delete the other variable's row.
 
Top