Solved: VBA Search

[Stromma]

Hi all

I've been using this to find numbers in a specified Range 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("D3:D4190").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 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:
Public Sub test()
Dim c As String

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

With Sheets("Register").Range("D2:D4190")
.Parent.AutoFilterMode = False
.AutoFilter Field:=1, Criteria1:=c
End With
End Sub

[Stromma]

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

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

With Sheets("Register").Range("D2:D4190")
.Parent.AutoFilterMode = False
.AutoFilter Field:=1, Criteria1:=c
End With
End Sub
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!