VBA onchange - Troubleshoot

[ouvay]

Hello,

I've got this onchange bit of code I'm working on and its basically a simple index match
unfortunately, it doesn't work as 'subscription is out of range'

Couldn't anyone tell me where I'm going wrong?

TIA

Private Sub Worksheet_Change(ByVal Target As Range)


Dim WorkRng As Range
Dim Rng As Range

Set WorkRng = Intersect(Application.ActiveSheet.Range("C6:C5000"), Target)
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, -1).Value = calcSlip(Rng.Value)
        Else
            Rng.Offset(0, -1).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If

End Sub

Function calcSlip(ID As Long) As Long

lastRow = SalesList.Cells(Rows.Count, 1).End(xlUp).Row
Dim a() As Variant
ReDim a(1 To lastRow, 1 To 16) As Variant
a = SalesList.ListObjects(1).DataBodyRange

For i = 2 To lastRow
    'Debug.Print a(i, 3)
    If a(i, 3) = ID Then
        calcSlip = a(i, 2)
    End If
Next i

End Function

 

[Alex Blakenburg]

Try changing your For loop to this:

For i = 1 To UBound(a)

 

[ouvay]

Try changing your For loop to this:

For i = 1 To UBound(a)

ahhhh! >.<

I made a boo boo!

Good catch! Thanks a bunch

 

[Alex Blakenburg]

No problem. Glad we could help.