How to update cell value in Excel table using VBA and Match function?

Question

not a VBA pro here, but doing my best...

The goal is to create a Macro that updates a cell value in a table based on the table row variable from Application.Match function, which I am also struggling with. Here's what I have so far and where I'm lost (also commented into the code).

1. I can't seem to get the match function to set my TargetRw variable to the matched row in the table. As it is currently i'm getting 'Type Mismatch', but I've attempted several different configurations and received a variety of different errors.

2. If i can get the match to work, I'd like to be able to set the cell value of the TargetRw and table Column "Reviewed Rate" = to the value held in the 'Rate' variable. I haven't been able to find much online regarding how to reference a table range like this in order update a cell value.

   Sub ReviewTracker()
   
   Dim Acell As Variant
   Dim TargetRw As Long
   Dim Rate As Variant
   Dim MACMtable, RCtable, TargetTable As ListObject
   Dim LUTables As Worksheet
   
   
       Set LUTables = ThisWorkbook.Sheets("LookupTables")
   
       Set MACMtable = LUTables.ListObjects("MACM_Lookup")
       Set RCtable = LUTables.ListObjects("RC_Lookup")
   
       Asht = ActiveSheet.Name
       Acell = ActiveCell.Value
       Rate = ActiveCell.Offset(0, -3).Value
   
       If Asht = "Rate Codes" Then
       Set TargetTable = RCtable
       Else
       If Asht = "MACMs" Then
       Set TargetTable = MACMtable
       End If
           End If
   
       ***''' Can't get the TargetRw variable below to work... Type Missmatch'''***
       TargetRw = Application.Match(Acell, TargetTable.ListColumns(1), 0)
   
       With TargetTable
           ******'''I am trying to figure out how to set the cell corresponding to the row: TargetRw & Column 6 (name: "Reviewed Rate") to the value of the variable 'Rate'******
   
       .DataBodyRange.Cells(TargetRw, 6) = Rate.Value '''This doesn't seem to work, but hopefully illustrates the goal'''
   
       End With
   

   End Sub

There are 2 tables on a single worksheet (variable: 'LUTables'). One or the other would be updated depending on the activesheet at the time the Macro was initiated. Both have a column named "Reviewed Rate", which is also the 6th column in each table.

Any assistance would be very much appreciated!

Answer 1

TargetTable.ListColumns(1)

should be

TargetTable.ListColumns(1).DataBodyRange

A ListColumn is not the same thing as a Range

Untested:

Sub ReviewTracker()

    Dim Acell As Variant, Asht As String
    Dim TargetRw As Variant '***
    Dim Rate As Variant
    Dim TargetTable As ListObject
    Dim LUTables As Worksheet

    Set LUTables = ThisWorkbook.Sheets("LookupTables")

    Asht = ActiveSheet.Name
    Acell = ActiveCell.Value
    Rate = ActiveCell.Offset(0, -3).Value

    If Asht = "Rate Codes" Then
        Set TargetTable = LUTables.ListObjects("RC_Lookup")
    ElseIf Asht = "MACMs" Then
        Set TargetTable = LUTables.ListObjects("MACM_Lookup")
    End If

    TargetRw = Application.Match(Acell, TargetTable.ListColumns(1).DataBodyRange, 0)

    If Not IsError(TargetRw) Then
        TargetTable.DataBodyRange.Cells(TargetRw, 6) = Rate '### no .Value
    End If

End Sub