loop through matches

[geebee]

hi,

i have a formula that is lke MATCH(B2)...

the only problem is that there can be more than one occurence of the item we
are matching in the column. so i would like to know how i can write code to
loop through to get the matches if there is more than one match.

thanks in advance,
geebee

 

[Barb Reinhardt]

Here's a starting point. It's crude and I'd probably add some error
checking, but it should work. If you want something returned, this can be
modified to be a function, but I'd need to know what you want returned.

Sub Macro1()
'
Dim aWS As Worksheet

Dim myRange As Range
Dim r As Range

Set aWS = Worksheets("Sheet1") '<~~change here
Set myRange = aWS.Range("A1:A100") '<~~~change here
For Each r In myRange
If LCase(r.Value) = "mystring" Then '<~~~change here
MsgBox ("Match found at " & r.Address)
End If
Next r

End Sub

 

[Dave Peterson]

You could also use the equivalent of Edit|Find in your code. This usually works
much faster if there are lots of cells, but not many matches.

Here's a sample:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim FoundCell As Range
Dim WhatToFind As String
Dim FirstAddress As String

WhatToFind = "asdf"

With Worksheets("sheet1")
Set myRng = .Range("a:a") 'say
End With

With myRng
Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "not found in: " & myRng.Address(0, 0)
Else
FirstAddress = FoundCell.Address
Do
'do your stuff that does all the work
'and put it into the adjacent(?) cell
FoundCell.Offset(0, 1).Value = "whatever you need here"

'look for more
Set FoundCell = .FindNext(After:=FoundCell)

If FoundCell Is Nothing Then
Exit Do
ElseIf FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With

End Sub

 

[JP]

This site has some useful code:

http://www.ozgrid.com/VBA/find-method.htm


HTH,
JP