CODEWITHSUNDEEP
vbaexcel
IAntoniazzi
IAntoniazzi

Reputation: 137

Excel VBA - Macro Runs but has no effect

I'm new to Excel VBA and this is my first macro, so please forgive me if I've made a very obvious mistake. I have the following code to compare to worksheets and, if a match is found, to make a note on one of the sheets. It runs with no errors, but the changes are not being made. I can't see where I've gone wrong. Thanks in advance for the help.

Sub invalid()

Dim i As Integer
Dim j As Integer

Dim main As Worksheet
Dim invalid As Worksheet

i = 2
Set main = ThisWorkbook.Worksheets(1)
Set invalid = ThisWorkbook.Worksheets(2)

Do
    j = 2
    Do
        If LCase$(invalid.Cells(i, 1).Value) = LCase$(main.Cells(j, 13).Value) Then
            main.Cells(j, 14).Value = "Invalid Email"
        End If
        j = j + 1
    Loop While main.Cells(j, 2) = Not Null
    i = i + 1
Loop While invalid.Cells(i, 2) = Not Null


End Sub

Upvotes: 1

Views: 412

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Try this, it removes one of the loops:

Sub invalid()

Dim i As Long
Dim j As Long
Dim lRow As Long
Dim main As Worksheet
Dim invalid As Worksheet

Set main = ThisWorkbook.Worksheets(1)
Set invalid = ThisWorkbook.Worksheets(2)
lRow = main.Cells(main.Rows.Count, 13).End(xlUp).Row

For i = 2 To lRow
    j = 0
    On Error Resume Next
    j = Application.WorksheetFunction.Match(main.Cells(i, 13), invalid.Range("A:A"), 0)
    On Error GoTo 0
    If j > 0 Then main.Cells(i, 14) = "Invalid Email"
Next i

End Sub

Upvotes: 1

Related Questions