MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA "Find" method with use of "After"

Posted by Mike Merkel on July 17, 2001 10:42 AM

Hi,
there is a problem that I just can't figure out. When I use the Find method and I want to include the After parameter it only works if I enter the cell as [A2] for example. But I would like to keep that cell variable so I can search from different points in my table on. But whatever I tried I always get error 1004, that the find method can not be applied to the range object. When I leave out the After part everyhting works fine. I know that I have to reference a single cell, but I can't get a variable to take that place.
Pleeeeeeeeeease help
I gonna lose all my hair before I get that one right.
Thanks
Mike

Posted by Russell on July 17, 2001 10:52 AM

I'm not sure exactly what you are doing, but this looks like it can help (straight from Excel help) - use FindNext:

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

Hope this helps,

Russell

Posted by mmerkel on July 17, 2001 11:12 AM

Thanks,
but I have different procedures which set the variable that would be used in the After parameter. If I would use the FindNext I would be limited to only one procedure using that value.
Mike

Posted by Russell on July 17, 2001 11:35 AM

Then I suggest that you do it a different way than using Find. You can maneuver through a range in many different ways. You can then do you own "find" by comparing each cell to your value (using Cells(Row, Column), or even Range(Offset...).

Hope you figure it out,

Russell

,

Posted by Ivan F Moala on July 17, 2001 7:34 PM

What is the code you are using and what ranges ?
are you searching......


Ivan

Posted by Mike on July 19, 2001 10:29 AM

Found it, thanks for your help (nt)