Find method, After parameter

[melisfreed]

I want to use a returned address as the "After" property with the Fin
method. I am having problems figuring this out. I do explaining best b
example:

First I get the address of the string I want to find (this part work
great):
With Worksheets("data").Range("G1:G3000")
Set c = .find("BH")
BHAddress = c.Address
End With

Next, I want to use BHAddress as my starting point for my new search
Here I want to search for "BA". I have tried two things (See below
both I am getting errors with:

With Worksheets("data").Range(" BHAddress :G3000")
Set c = .find("BA")
BAAddress = c.Address
End With

I get an error here with the first line: "Error 100
application-defined or object-defined error"

OR

With Worksheets("data").Range("G1:G3000")
Set c = .find("BA", BHAddress )
BAAddress = c.Address
End With
I get an error here with the second line: "Error 1004 Unable to get th
Find property of the Range class"

I thought about returning the Row and the Column separately and the
concatenating them together to make the address if it is not returne
in the proper form. I see currently it is returned as $Column$Row
Perhaps that is why I am getting problems?

I am using Excel 2000, Win 2000.

Thanks so much in advance! I am sure it is something silly

 

[Charles]

melisfreed,


Try

With Worksheets("data").Range(BHAddress & ":" & "G3000")

HTH

Charle

 

[Dick Kusleika]

melisfred

I want to use a returned address as the "After" property with the Find
method. I am having problems figuring this out. I do explaining best by
example:

First I get the address of the string I want to find (this part works
great):
With Worksheets("data").Range("G1:G3000")
Set c = .find("BH")
BHAddress = c.Address
End With

Next, I want to use BHAddress as my starting point for my new search.
Here I want to search for "BA". I have tried two things (See below)
both I am getting errors with:

With Worksheets("data").Range(" BHAddress :G3000")
Set c = .find("BA")
BAAddress = c.Address
End With

I get an error here with the first line: "Error 1004
application-defined or object-defined error"

That would have to be .Range(BHAddress & ":G3000"), but your next method is
better.

OR

With Worksheets("data").Range("G1:G3000")
Set c = .find("BA", BHAddress )
BAAddress = c.Address
End With
I get an error here with the second line: "Error 1004 Unable to get the
Find property of the Range class"

The after argument takes a range object, not a string (e.g. Address).

With Worksheets("data").Range("G1:G3000")
Set c = .Find("BH")
Set c = .FindNext(c)
End With

Now c will be a range that contains the first BA after the first BH.

See here for more on the Find method

http://www.dicks-blog.com/excel/2004/03/the_find_method.html