Searching in Two Non-Continguous Columns

[Guest]

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]

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

 

[Guest]

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