CODEWITHSUNDEEP
vbaexcel
Bubs
Bubs

Reputation: 105

Excel VBA Error on my Msgbox line

I'm getting an Application-defined or object-defined error on my Msgbox line. I have both InputBox variables declared as String. I tried changing Sheets(sheetname1) to an actual sheet name but same error. I tried everything I know, I'm puzzled with this error. Any help is appreciated.

sheetname1 = Application.InputBox("Enter the name of your first sheet.")
sheetname2 = Application.InputBox("Enter the name of your second sheet.")
For i = 1 To 100
    For j = 1 To 100
        If Not Sheets(sheetname1).Cells(i, j).Value = Sheets(sheetname2).Cells(i, j).Value Then
            Sheets(sheetname1).Select
            Cells(i, j).Select
            With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            ans = MsgBox("Cells " & i & "," & j & " do not match." & vbNewLine & "The value on " & sheetname1 & " is " & Sheets(sheetname1).Cells(i, j).Value & " and the value on " & sheetname2 & " is " & Sheets("sheetname2").Cells(i, j).Value, vbOKCancel + vbQuestion)
            If ans = vbCancel Then Exit Sub
            Else
                GoTo skip1
        End If
skip1:
    Next j
Next i

Upvotes: 1

Views: 81

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

As @BruceWayne correctly identified, you're using undeclared variables. Use Option Explicit.

Let me rephrase that...

ALWAYS use Option Explicit

Simple: stick the words Option Explicit at the top of every single module you ever see, and VBA will refuse to compile code where you're using an undeclared variable.

In this instance, you're referring to i1 and j1, which both contain the non-value Empty, since they're undeclared and uninitialized variants.

Here are the variables you need to declare:

Dim i As Long
Dim j As Long
Dim ans As vbMsgBoxResult
Dim sheetName1 As String
Dim sheetName2 As String

Without Option Explicit, VBA happily compiles a typo and takes it as "oh, I've never seen that guy, must be a new variable!"

Upvotes: 3

Related Questions