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).

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.

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!

hi. Application.Match returns a double and your TargetRw is a long. replace Dim TargetRw As Long for Dim TargetRw As double and try. good luck — Luis Curado
– Luis Curado, Commented Oct 16, 2019 at 16:30
Something to point out, your line 'Dim MACMtable, RCtable, TargetTable As ListObject', dimensions TargetTable as a list object and the others as variants, you need to specify each one like Dim MACMtable As ListObject, RCtable As ListObject, TargetTable As ListObject — Jeremy Hodge
– Jeremy Hodge, Commented Oct 16, 2019 at 16:38
If there’s no match then Match returns an error value, so the receiving variable should be a variant, and you need to test for an error before proceeding — Tim Williams
– Tim Williams, Commented Oct 16, 2019 at 16:57

Tim Williams · Accepted Answer · 2019-10-16 20:45:28Z

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

edited Oct 16, 2019 at 20:45

answered Oct 16, 2019 at 17:13

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

joe1250 Over a year ago

Thanks everyone for all your help. The TargetRw variable is now working as expected however Tim's suggested line of 'TargetTable.DataBodyRange.Cells(TargetRw, 6) = Rate.Value' is generating Run-Time Error '424': Object required...?

joe1250 Over a year ago

hey @Tim, would you happen to know if this error is in reference to the TargetRw variable or the Table? or neither? thanks

Tim Williams Over a year ago

Sorry my bad - missed removing the .Value from Rate (this just a plain variable and has no Value property)

joe1250 Over a year ago

the weird thing is that i'd tried that already and when it executed that line, it initiated a UDF that i have in another module, which is named UserName, but takes Reference as Range... not sure how or why it called that from Rate... I thought might have something to do with the Rate variable's name and/or type so i tried renaming to myRate and changing the Dim type (Rate will either be type integer or decimal depending), but no luck with all the various combinations i tried. It either throws the error or calls the UDF

joe1250 Over a year ago

I also tried replacing = Rate with something else like = Now() and it still calls the UDF

|

Collectives™ on Stack Overflow

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

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related