Excel VBA FOR loop with IF statement to match with data in another column and append a seperate column

Question

So I have two columns of name data with the second matching the first only sparingly. I'd like to create an iterator that loops through the first column and the second column simultaneously and, upon finding a match, appends a different column, of the same row, with a specific phrase.

Data looks similar to this

Row 1             Row 2            Row 3

Kyle Hammer                        John Doe
John Doe                           Gary Wilson
Bill Walter
Gary Wilson

I'm pretty new to VBA. Done a lot of javascript but never had to work with excel. I tried the code below but keep getting a run time error 424?

Sub Iterator()
'
' Iterator Macro
'
Set dbsheet2 = ThisWorkbook.Sheets("Sheet1")

lr1 = dbsheet2.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = dbsheet2.Cells(Rows.Count, 4).End(xlUp).Row

For x = 1 To lr1
    act1 = dbsheet2.Cells(x, 1)

    For y = 1 To lr2
        act2 = dbsheet1.Cells(y, 1)

        If act2 = act1 Then
            dbsheet2.Cells(y, 6).Value = "Match"

        Else
            dbsheet2.Cells(y, 6).Value = "Nothing here for me Dawg"
        End If
    Next y
Next x
End Sub

I'm hoping to simply write "match" to column 6 when there is a match between the names in column 1 and 3.

Your first table is confusing. You wrote Row1, Row2, Row3 at the top, aren't those columns? And if so, then column 2 is empty :S Um. Also in VBA you need to declare objects every time — Danny Papadopulos
– Danny Papadopulos, Commented Aug 29, 2019 at 18:53
Also if you want to check whether an entry is the same in the same row of Column1 and Column2 you don't need two loops, just one loop is enough. — Danny Papadopulos
– Danny Papadopulos, Commented Aug 29, 2019 at 18:57

Wizhi · Accepted Answer · 2019-08-29 21:07:45Z

Assuming your data is in Column A and Column D

Change the line act2 = dbsheet1.Cells(y, 1) -> act2 = dbsheet2.Cells(y, 4).

dbsheet1 is not defined. Therefore you get an error. Also I think you want the second loop to go through column D (as you do lr2 on the 4 column), therefore I changed to (y, 4).

Only dbsheet2 is defined as Set dbsheet2 = ThisWorkbook.Sheets("Sheet1").

EDIT: I think you should amend the code... as it make more sense to have: x value: dbsheet2.Cells(x, 6).Value = "Match" and dbsheet2.Cells(x, 6).Value = "Nothing here for me Dawg"

It's a little bit unclear what you want to do but I guess something like this:

Sub Iterator()
'
' Iterator Macro
'
Set dbsheet2 = ThisWorkbook.Sheets("Sheet1")

lr1 = dbsheet2.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = dbsheet2.Cells(Rows.Count, 4).End(xlUp).Row

For x = 1 To lr1
    act1 = dbsheet2.Cells(x, 1)

    For y = 1 To lr2
        act2 = dbsheet2.Cells(y, 4)

        If act2 = act1 Then
            dbsheet2.Cells(x, 6).Value = "Match"

        Else
            dbsheet2.Cells(x, 6).Value = "Nothing here for me Dawg"
        End If
    Next y
Next x

End Sub

Collectives™ on Stack Overflow

Excel VBA FOR loop with IF statement to match with data in another column and append a seperate column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related