CODEWITHSUNDEEP
vbaexcel
Nishant
Nishant

Reputation: 19

On Error Goto Line1 not working in VBA

I am working on a VBA code to identify changes in data in two different excel sheets. The code matches the data in first columns (A2 then A3 and then A4....) and checks the row for data in the other sheet. However when the code gets interrupted when it is unable to find data that is in the first sheet but not in the second sheet. The find function returns nothing.

I need your help on this as I am unable to get On Error Goto Line1 statement working.

Best, Nishant

VBA Code for the same:

Sub Compare()
    Var = 2
    Sheets("Sheet1").Select
    Do Until Range("A" & Var).Value = ""
        Sheets("Sheet1").Select
        var1 = Range("A" & Var).Value
        Sheets("Sheet2").Select
        Range("A1").Select

        On Error GoTo Line1
        var2 = Range("A:A").Find(What:=var1).Row
        For i = 1 To 6
            If Worksheets("Sheet1").Cells(Var, i).Value <> _
               Worksheets("Sheet2").Cells(var2, i) Then
                Worksheets("Sheet2").Cells(var2, i).Interior.ColorIndex = 3
            End If
        Next

Line1:
        Var = Var + 1
    Loop
End Sub

Upvotes: 0

Views: 1092

Answers (1)

YowE3K
YowE3K

Reputation: 23974

You are not using error-handling correctly. For instance, after hitting an error you are attempting to continue processing as if nothing has occurred. That will cause an untrappable error when the next error occurs.

It is much better to avoid error handling unless absolutely necessary, and in this case it isn't necessary.

Sub Compare()
    Dim FindRange As Range
    Dim Var As Long
    Dim Var2 As Long
    Var = 2
    Do Until Worksheets("Sheet1").Range("A" & Var).Value = ""
        var1 = Worksheets("Sheet1").Range("A" & Var).Value

        Set FindRange = Worksheets("Sheet2").Range("A:A").Find(What:=var1)
        If Not FindRange Is Nothing Then
            var2 = FindRange.Row
            For i = 1 To 6
                If Worksheets("Sheet1").Cells(Var, i).Value <> _
                   Worksheets("Sheet2").Cells(var2, i) Then
                    Worksheets("Sheet2").Cells(var2, i).Interior.ColorIndex = 3
                End If
            Next
        End If
        Var = Var + 1
    Loop
End Sub

Upvotes: 1

Related Questions