loop through matches
 

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

loop through matches
 

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
--
HTH,
Barb Reinhardt



"geebee" wrote:

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

loop through matches
 

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

geebee wrote:

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

--

Dave Peterson

loop through matches
 

This site has some useful code:

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


HTH,
JP


"geebee" wrote:

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