Excel Macro Subscript out of range error

I have below macro,getting script out of range error in If arr(0) <> opt Then arr(0) = VAL_DIFF

if i see the length of that array it is showing 2.I dont understand why i am not able to access arr(0),As per i know Array always starts with 0.I able to print arr(1),arr(2) values.

Below macro able to findout similar records and copied to sheet2.Here i also wants to highlight with color in sheet1. Please help me.

Option Base 1
Sub Tester()

    Const COL_ID As Integer = 1
    Const COL_SYSID As Integer = 2
    Const COL_STATUS As Integer = 4
    Const COL_OPTION As Integer = 3
    Const VAL_DIFF As String = "XXdifferentXX"

    Dim d As Object, sKey As String, id As String
    Dim rw As Range, opt As String, rngData As Range
    Dim rngCopy As Range, goodId As Boolean
    Dim FirstPass As Boolean, arr

        With Sheet1.Range("A1")
            Set rngData = .CurrentRegion.Offset(1).Resize( _
                             .CurrentRegion.Rows.Count - 1)
        End With
        Set rngCopy = Sheet1.Range("F2")

        Set d = CreateObject("scripting.dictionary")
        FirstPass = True

    redo:
        For Each rw In rngData.Rows
开发者_C百科
            sKey = rw.Cells(COL_SYSID).Value & "<>" & _
                   rw.Cells(COL_STATUS).Value

            If FirstPass Then
              'Figure out which combinations have different option values
              '  and at least one record with id=US or CHN
              id = rw.Cells(COL_ID).Value
              goodId = (id = "US" Or id = "CHN")
              opt = rw.Cells(COL_OPTION).Value

              If d.exists(sKey) Then
                  arr = d(sKey) 'can't modify the array in situ...
                  If arr(1) <> opt Then arr(1) = VAL_DIFF
                  If goodId Then arr(2) = True
                  d(sKey) = arr 'return [modified] array
              Else
                  d.Add sKey, Array(opt, goodId)
              End If

            Else
              'Second pass - copy only rows with varying options
              '  and id=US or CHN
              If d(sKey)(2) = VAL_DIFF And d(sKey)(1) = True Then
                  rw.Copy rngCopy
                  Set rngCopy = rngCopy.Offset(1, 0)
              End If
            End If

        Next rw
        If FirstPass Then
            FirstPass = False
            GoTo redo
        End If

    End Sub

---

Stop using Option Base 1 at the top of your modules. It might seem to make things easier, but it only ends up causing confusion. I already gave you the answer to coloring the range - please try to look things up and try them out before posting here.

---

To check the lower bound position of an array, use Lbound(arr).

Also, you did not initialize arr as an array. Initialize it like so: Dim arr() As Variant.

You could also specify the lower dimension and upper dimension of the array. Dim arr(3 To 10) As Variant. This creates an array of 7 elements starting at position zero. I suggest learning more about arrays in VBA by doing a Google search.