CODEWITHSUNDEEP
vbaloopsexcel
Docmur
Docmur

Reputation: 346

Excel macro using loops

So I have two columns 17&18 where I have multiple rows such as:

17  |  18<br>
ttt | xxx<br>
tty | xxy<br>
eer | eet<br>
fff | fft<br>

etc... What I want to do is start at Row 2 Column 17, grab ttt and then see if that occurs again in either column 17 or 18. If it doesn't I need to display a message to the user and if it does, say Row 20 Column 18 I need to ignore it and mark that I've already found this value and don't want to come across it again when I get down there.

I hope this makes sense ...

I think the right thing to do is to use a Do loop and look at something like:

    Dim X As Range
    Do While Cells(X, 17) <> ""
        Do While Cells(X,18) <> ""
            Cells.Find(What:=X.Value, After:=activeCell).Active
        Loop
    Loop

Has anyone tried to do this before?

Upvotes: 0

Views: 86

Answers (1)

David Zemens
David Zemens

Reputation: 53663

I wouldn't use the range .Find method for this. Use simply the Application.Match function or the WorksheetFunction.CountIf function. In order to ignore it on second/subsequent passes, you will need to store the list of values to ignore in memory, I would recommend using a dictionary object for this.

Something like this perhaps (untested):

Sub foo()
Dim column1 as Range
Dim rngToCheck as Range
Dim r as Range
Dim dict as Object


'use a dictionary object to keep track of the items that appear more than once
Set dict = CreateObject("Scripting.Dictionary")

Set column1 = Range("A1:A100") 'Modify as needed -- only the first column

Set rngToCheck = Range("A1:B100") 'Modify as needed -- both columns

'Check each value in column1 against the entire range
For each r in column1
    'ignoring anything that already has been added to the dictionary
    If not dict.Exists(r.Value) Then
        If WorksheetFunction.CountIf(rngToCheck, r.Value) > 1 then 
            'if it appears more than once then add it to the dictionary so to ignore
            ' it the next time the macro encounters this value:
            dict(r.Value) = dict(r.Value)
        Else
            'if this value only appears once, then it doesn't appear anywhere else, _
            ' so msgbox to the user. Modify msgbox as needed:
            MsgBox r 

        End If
    End If
Next

End Sub

Upvotes: 1

Related Questions