Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Find & FindNext in a form | Excel Programming | |||
Nesting Find and FindNext | Excel Programming | |||
Using 'Find' and 'FindNext' in vba | Excel Programming | |||
Find, Findnext VBA Loop | Excel Programming |