vba - 使用动态引用将 Excel 公式转换为 VBA 代码

Question

我的代码变量中有 MyCol获取在用户表单中选择的按月列的编号。例如，10 月在 U 列，11 月在 V 列。我记录了一个公式，选择的月份是其中的一部分，但这是一个问题，因为 RC 格式具有特定引用，而我的变量是整数。

我希望公式是动态的。

这是公式(U 列表示选择的月份):

=IFNA(IF(VLOOKUP(AA2,sheet1!F:F,1,0)=AA2,U2,0),0)

这是代码的相关部分:

Dim MonthName As String
Dim myCol As Integer

MonthName = ListMonth.Value

With MainWB.Worksheets("sheet2")
    .Activate
    .Range("L1:W1").Find(MonthName, , xlValues, xlWhole).Activate
End With
ActiveCell.Select
myCol = Selection.Column
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
    "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[" & myCol & "],0),0)"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & MLR), Type:=xlFillDefault

Answer 1

更换你的:

Range("AB2").Select
ActiveCell.FormulaR1C1 = _
    "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[" & myCol & "],0),0)"

和:

Range("AB2").FormulaR1C1 = _
            "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[-" & Range("AB2").Column - myCol & "],0),0)"

然而 , 如果你想让你的代码运行得更快，同时也避免所有不必要的 Activate , ActiveCell , Select ，试试下面的代码:

Dim FindRng As Range

MonthName = ListMonth.Value

With MainWB.Worksheets("sheet2")
    Set FindRng = .Range("L1:W1").Find(MonthName, , xlValues, xlWhole)
End With

If Not FindRng Is Nothing Then
    myCol = FindRng.Column
Else ' find was not successful finding the month name
    MsgBox "Unable to find " & MonthName, vbCritical
    Exit Sub
End If

Range("AB2").FormulaR1C1 = _
            "=IFNA(IF(VLOOKUP(RC[-1],sheet1!C[-22],1,0)=RC[-1],RC[-" & Range("AB2").Column - myCol & "],0),0)"