VBA Lookup using Tables

[Skybluekid]

Hi All,

I am using the code below to lookup a value from a Table.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LU As String
Dim Auth As Variant
Dim LU2 As String
Dim Tbl As ListObject
Dim Rng As Variant
Dim Rng2 As Variant
Dim FT As Variant

Set Tbl = Sheets("Extra").ListObjects("AuthorityTable")
Set Rng = Tbl.ListColumns("Fining Authority")
Set Rng2 = Tbl.ListColumns("Fine Type")

'Insert Authority
If Not Intersect(Target, Range("C:C")) Is Nothing Then
LU = Left(Target.Value, 2)
Auth = Application.VLookup(LU, [AuthorityTable], 2, 0)
If IsError(Auth) Then Auth = "Choose from List"
Target.Offset(, 4).Value = Auth
End If




'Insert Fine Type
If Not Intersect(Target, Range("C:C")) Is Nothing Then
LU2 = Target.Offset(, 4).Value
FT = Application.WorksheetFunction.Index(Sheets("Extra").Range("Rng"), Application.WorksheetFunction.Match(LU2, Sheets("Extra").Range("Rng2"), 0))
If IsError(FT) Then FT = "Choose From List"
Target.Offset(, 8) = FT
End If


End Sub

The code crashes on this line

FT = Application.WorksheetFunction.Index(Sheets("Extra").Range("Rng"), Application.WorksheetFunction.Match(LU2, Sheets("Extra").Range("Rng2"), 0))


saying its an Application or Object defined error.


What the code does is to Look at the first to characters of the Target, then look up the Authority, then it will use that Authority to return a Fine Type, from the same table. The 1st Lookup uses Columns 1 and 2 of the table, where as the send lookup uses columns 2 and 3, hence using Index & Match rather than Vlookup.

I am little stumped.

Thanks in Advance.

 

[Fluff]

Try

FT = Application.Index(Rng, Application.match(LU2, Rng2, 0))

 

[Skybluekid]

Thank you Fluff

 

[Fluff]

You're welcome