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
|