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

 

[bebo021999]

Function calcSlip(ID As Long) As Long
lastRow = SalesList.Cells(Rows.Count, 1).End(xlUp).Row
'Dim a() As Variant
Dim a
'ReDim a(1 To lastRow, 1 To 16) As Variant
'a = SalesList.ListObjects(1).DataBodyRange
a = SalesList.ListObjects(1).DataBodyRange.value
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

 

[ouvay]

Function calcSlip(ID As Long) As Long
lastRow = SalesList.Cells(Rows.Count, 1).End(xlUp).Row
'Dim a() As Variant
Dim a
'ReDim a(1 To lastRow, 1 To 16) As Variant
'a = SalesList.ListObjects(1).DataBodyRange
a = SalesList.ListObjects(1).DataBodyRange.value
For i = 2 To lastRow
   
    If a(i, 3) = ID Then
        calcSlip = a(i, 2)
    End If
Next i
End Function

if a(i,3) = ID Then --- line still reads as an error

 

[bebo021999]

Which line is highlighted?

 

[ouvay]

Which line is highlighted?

The if statement line reads as an error

 

[bebo021999]

Is the "SalesList" and ActiveSheet.Range("C6:C5000") in the same sheet?
Do they intersect?

 

[ouvay]

Is the "SalesList" and ActiveSheet.Range("C6:C5000") in the same sheet?
Do they intersect?

no, those are different sheets

 

[Alex Blakenburg]

Which if statement ?
Try
For Each Rng In WorkRng.Cells

 

[ouvay]

Which if statement ?
Try
For Each Rng In WorkRng.Cells

the if statement in the function

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

 

[ouvay]

so I tried my code again using ranges instead of arrays and it works .. so I guess the problem lies with using an array? would anyone be able to explain why?