Solved: VBA Search

[Stromma]

Hi all

I've been using this to find numbers in a specified Range[vba] Dim c As String
Dim MySearch As Range
If txtSearch.Value = "" Then Exit Sub
c = txtSearch.Value
If txtSearch.Value <> "" Then
With Sheets("Register")
Set MySearch = .Range("D34190").Find(What:=c, LookIn:=xlValues, SearchDirection:=xlNext)
If Not MySearch Is Nothing Then
MySearch.Offset(0, 3).Activate
Else
MsgBox "Found no match for: " & c
End If
End With
End If[/vba] It worked well as long as all the numbers were unique. But now they can show up several times, which means that I need to have the ability to search for the next match as well.

How do I do this?

/Stromma

[JONvdHeyden]

What do you want to do wth the cell adjacent the match in column G? At the moment all you do is activate the cell. That means looping through each matching cell will simply result in the last match being activated.

[Bob Phillips]

Look at Find in VBA help and note the FindNext code.

[Stromma]

Hi JONvdHeyden and XLD

Thanks for your replies!

What do you want to do wth the cell adjacent the match in column G? At the moment all you do is activate the cell. That means looping through each matching cell will simply result in the last match being activated.

I have to edit those cells manually so finding and activating them is the only thing I need to do.


Problem was solved with help from Jerry Beaucaire on the Mr.Excel Forum:

    Dim c As String
    Dim MySearch As Range
    Dim MyFirst As Range
 
    If txtSearch.Value = "" Then Exit Sub
    c = txtSearch.Value
    If txtSearch.Value <> "" Then
        With Sheets("Register")
            Set MySearch = .Range("D3:D4190").Find(What:=c, LookIn:=xlValues, SearchDirection:=xlNext)
            If Not MySearch Is Nothing Then
                Set MyFirst = MySearch
                Do
                    MySearch.Offset(0, 3).Activate
                    If MsgBox("Found: " & c & ", search for more?", vbYesNo, "Continue?") = vbNo Then
                        Set MySearch = Nothing
                        Set MyFirst = Nothing
                        Exit Sub
                    Else
                        Set MySearch = .Range("D3:D4190").FindNext(MySearch)
                    End If
                Loop Until MySearch.Address = MyFirst.Address
 
                MsgBox "Last value found."
                Set MySearch = Nothing
                Set MyFirst = Nothing
                Exit Sub
            Else
                MsgBox "Found no match for: " & c
            End If
        End With
    End If

[JONvdHeyden]

Personaly I would have gone with a filter method to show all items in one go:
[VBA]Public Sub test()
Dim c As String

If txtSearch.Value = "" Then Exit Sub
c = txtSearch.Value

With Sheets("Register").Range("D24190")
.Parent.AutoFilterMode = False
.AutoFilter Field:=1, Criteria1:=c
End With
End Sub[/VBA]

[Stromma]

Personaly I would have gone with a filter method to show all items in one go:
[vba]Public Sub test()
Dim c As String

If txtSearch.Value = "" Then Exit Sub
c = txtSearch.Value

With Sheets("Register").Range("D24190")
.Parent.AutoFilterMode = False
.AutoFilter Field:=1, Criteria1:=c
End With
End Sub[/vba]

I thought of that and I do use filter to print different selections.

This case is a bit different because most of the time I just need to edit one of the cells attached to the matching number. Let's say that number 358 exists in 50 different cells (rows). Using filter I then have to look through all 50 rows to find the cell to edit. But with the search I get the matches presented one by one, and I feel is much easier to find the real match (cell) to edit that way.

Thanks for your suggestion though!