Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching in Two Non-Continguous Columns
I would like to attach a button to a worksheet that executes a string search
in two non contiguous columns. Once an item is found, theoretically, the search would continue after the active cell each time the button is pressed. The code I wrote does this imperfectly. Each time the button is pressed, it moves on to the next item found in the first of the two columns but will not continue to find through the second column in the range (it stops at the first cell in the second column and then returns to the first column and continues the search). Is there a way to search through both columns? Any help received would be most appreciated. Thank you. Here is the code: Dim SearchTarget As String MyRow = Selection.Row Range("G" & MyRow).Activate Set Rng = Range("G:G,O:O") 'search columns G and O With Rng Set FoundCell = .Cells.Find(What:=SearchTarget, _ After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then MsgBox SearchTarget & " was not found." Else FoundCell.Activate End If |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching in Two Non-Continguous Columns
I think you'll have to keep track of where you found that searchtarget (if I
understood correctly???). Then continue searching from that point. One way is to use a Static variable. It's not reset when the procedure finishes. Option Explicit Sub testme2() Dim SearchTarget As String Dim myRow As Long Dim Rng As Range Static PrevCell As Range Dim FoundCell As Range SearchTarget = "asdf" If PrevCell Is Nothing Then myRow = Selection.Row Set PrevCell = Range("G" & myRow) End If Set Rng = Range("G:G,O:O") 'search columns G and O With Rng Set FoundCell = .Cells.Find(What:=SearchTarget, _ After:=PrevCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then MsgBox SearchTarget & " was not found." Else FoundCell.Activate ' If PrevCell.Address = FoundCell.Address Then ' MsgBox "there's only one!" ' End If Set PrevCell = FoundCell End If End Sub Gregory Turk wrote: I would like to attach a button to a worksheet that executes a string search in two non contiguous columns. Once an item is found, theoretically, the search would continue after the active cell each time the button is pressed. The code I wrote does this imperfectly. Each time the button is pressed, it moves on to the next item found in the first of the two columns but will not continue to find through the second column in the range (it stops at the first cell in the second column and then returns to the first column and continues the search). Is there a way to search through both columns? Any help received would be most appreciated. Thank you. Here is the code: Dim SearchTarget As String MyRow = Selection.Row Range("G" & MyRow).Activate Set Rng = Range("G:G,O:O") 'search columns G and O With Rng Set FoundCell = .Cells.Find(What:=SearchTarget, _ After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False) End With If FoundCell Is Nothing Then MsgBox SearchTarget & " was not found." Else FoundCell.Activate End If -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching in Two Non-Continguous Columns
I was only partially aware of Static variables, but now I see how useful they
can be. Your modifications worked like a charm. Thank you very much again, Dave. Gregory Turk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
large function in non-continguous array? | Excel Discussion (Misc queries) | |||
Counting Continguous Cell Entries | Excel Discussion (Misc queries) | |||
Searching Columns | Excel Discussion (Misc queries) | |||
Average non continguous cells, excluding zero's | Excel Worksheet Functions | |||
Searching Columns | Excel Discussion (Misc queries) |