Excel macro using loops

Question

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?

Is the value in R2C17 the only value you care about or are you trying to find all unique values in those columns? — Mark Balhoff
– Mark Balhoff, Commented Jun 30, 2014 at 17:48

David Zemens · Accepted Answer · 2014-06-30 17:47:42Z

1

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

answered Jun 30, 2014 at 17:47

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Docmur Over a year ago

Excellent!! Thanks :-), This is what I was thinking of just didn't know how to do it

Collectives™ on Stack Overflow

Excel macro using loops

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related