Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
Loop through the columns and extract the matches | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming |