如何使用VBA和Match函数更新Excel表中的单元格值？

debugcn 发表于 Dev

joe1250

这里不是VBA专业人士，而是尽我所能...

目标是创建一个宏，该宏基于Application.Match函数中的表行变量来更新表中的单元格值，我也为此而苦苦挣扎。这是到目前为止，我丢失的内容（也已注释到代码中）。

我似乎无法获得match函数来将TargetRw变量设置为表中的匹配行。目前，我遇到“类型不匹配”的问题，但是我尝试了几种不同的配置，并收到各种不同的错误。

如果我可以使匹配生效，我希望能够将TargetRw的单元格值和表格“ Reviewed Rate”列=设置为'Rate'变量中保存的值。关于如何引用这样的表范围以更新单元格值，我一直在线上找不到很多。

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

结束子

单个工作表上有2个表（变量：“ LUTables”）。一个或另一个将根据启动宏时的活动表进行更新。两者都有一个名为“ Reviewed Rate”的列，这也是每个表中的第六列。

任何帮助将不胜感激！

蒂姆·威廉姆斯

TargetTable.ListColumns(1)

应该

TargetTable.ListColumns(1).DataBodyRange

ListColumn和Range不一样

未经测试：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。