Find / FindNext Methods
 

When using the Find / FindNext methods, they return the address of the value
that is being searched for (if found). Is it possible to extract the row
information from that address?

For example, let's say that in a spreadsheet, column A contains zip codes
and column G contains names. You need to come up with a listing of all the
names for zip code 12345. When you run the search, the address returned by
Find references the zip code found in column A, but you really need to get
the corresponding name out of column G. If you could get the row from each
address, and since the names are in column G, you could then pull the value
from that cell.

I haven't run across anything similar to this problem explained, and am not
sure it can be done. My last resort would be to do a line-by-line search,
but that isn't the most efficient way to search. Any ideas would be
appreciated.
--
Thanks,
Paul S

Find / FindNext Methods
 

Simplified pseudo code

Dim rng as Range
Dim rng1 as Range
Dim sAddr as String
With worksheets("Sheet1")
set rng = .range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with

set rng1 = rng.Find("22200", . . . )

if not rng1 is nothing then
sAddr = rng.Address
do
msgbox rng.Address & rng.offset(0,6).Address & vbNewline & _
rng.Value & ", " & rng.offset(0,6).Value
set rng1 = rng.FindNext(rng1)
Loop while rng1.Address < sAddr
End if

--
Regards,
Tom Ogilvy


"Paul S" wrote:

When using the Find / FindNext methods, they return the address of the value
that is being searched for (if found). Is it possible to extract the row
information from that address?

For example, let's say that in a spreadsheet, column A contains zip codes
and column G contains names. You need to come up with a listing of all the
names for zip code 12345. When you run the search, the address returned by
Find references the zip code found in column A, but you really need to get
the corresponding name out of column G. If you could get the row from each
address, and since the names are in column G, you could then pull the value
from that cell.

I haven't run across anything similar to this problem explained, and am not
sure it can be done. My last resort would be to do a line-by-line search,
but that isn't the most efficient way to search. Any ideas would be
appreciated.
--
Thanks,
Paul S

Find / FindNext Methods
 

Thanks, Tom. I should be able to make the adjustments and get this snippet
to fit in nicely.
--
Thanks,
Paul S


"Tom Ogilvy" wrote:

Simplified pseudo code

Dim rng as Range
Dim rng1 as Range
Dim sAddr as String
With worksheets("Sheet1")
set rng = .range(.cells(2,1),.cells(rows.count,1).End(xlup))
End with

set rng1 = rng.Find("22200", . . . )

if not rng1 is nothing then
sAddr = rng.Address
do
msgbox rng.Address & rng.offset(0,6).Address & vbNewline & _
rng.Value & ", " & rng.offset(0,6).Value
set rng1 = rng.FindNext(rng1)
Loop while rng1.Address < sAddr
End if

--
Regards,
Tom Ogilvy


"Paul S" wrote:

When using the Find / FindNext methods, they return the address of the value
that is being searched for (if found). Is it possible to extract the row
information from that address?

For example, let's say that in a spreadsheet, column A contains zip codes
and column G contains names. You need to come up with a listing of all the
names for zip code 12345. When you run the search, the address returned by
Find references the zip code found in column A, but you really need to get
the corresponding name out of column G. If you could get the row from each
address, and since the names are in column G, you could then pull the value
from that cell.

I haven't run across anything similar to this problem explained, and am not
sure it can be done. My last resort would be to do a line-by-line search,
but that isn't the most efficient way to search. Any ideas would be
appreciated.
--
Thanks,
Paul S