为什么这个带有定义变量的Vookup宏不工作

Question

我记录了以下宏：

Sub Macro2()
'
' Macro1 Macro
'

'
    Range("D108").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R[-12]C[-1]:R[-4]C[1],2,FALSE)"
    Range("E108").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R[-12]C[-2]:R[-4]C,3,FALSE)"
    Range("D108").Select
    Selection.AutoFill Destination:=Range("D108:D110"), Type:=xlFillDefault
    Range("D108:D110").Select
    Range("E108").Select
    Selection.AutoFill Destination:=Range("E108:E110"), Type:=xlFillDefault
    Range("E108:E110").Select
End Sub

然后，我尝试修改，因此我输入了两个单个数字：

Sub Macro1()
'
' Macro1 Macro
'

Dim row2start As Integer
row2start = InputBox("Write row to start this agenda", " ", "Enter your input text HERE")

Dim des2 As String
des2 = "D" & row2start + 1

Dim des3 As String
des3 = "E" & row2start + 1

Dim newActions As Integer
newActions = InputBox("count how many pending actions", " ", "Enter your input text HERE")
newActions2 = (row2start + 1) + newActions

Dim des4 As String
des4 = des2 & ":D" & newActions2

Dim des5 As String
des5 = des3 & ":E" & newActions2

    Range(des2).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R[-12]C[-1]:R[-4]C[1],2,FALSE)"
    Range(des3).Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R[-12]C[-2]:R[-4]C,3,FALSE)"
    Range(des2).Select
    Selection.AutoFill Destination:=Range(des4), Type:=xlFillDefault
    Range(des4).Select
    Range(des3).Select
    Selection.AutoFill Destination:=Range(des5), Type:=xlFillDefault
    Range(des5).Select
End Sub

我的问题是我运行它，什么也没有发生。我不知道为什么？有什么想法吗？ 从技术上讲，它是相同的记录宏，我只是用变量对其进行了更新。我已经仔细检查了变量，但找不到任何错误。 多谢

Answer 1

我相信您的宏观问题是您的公式都使用了相对地址，因此随着公式的复制，预期的查找范围不会被“查找”：

Sub Macro1()
' Macro1 Macro Modified
Dim row2start As Long
row2start = InputBox("Write row to start this agenda", " ", "Enter your input text HERE")

Dim des2 As String
des2 = "D" & row2start + 1

Dim des3 As String
des3 = "E" & row2start + 1

Dim newActions As Long, newActions2 As Long
newActions = InputBox("count how many pending actions", " ", "Enter your input text HERE")
newActions2 = (row2start + 1) + newActions

Dim des4 As String
des4 = des2 & ":D" & newActions2

Dim des5 As String
des5 = des3 & ":E" & newActions2

Range(des2, des4).FormulaR1C1 = "=VLOOKUP(RC[-1],R96C3:R104C5,2,FALSE)"
Range(des3, des5).FormulaR1C1 = "=VLOOKUP(RC[-2],R96C3:R104C5,3,FALSE)"
    
End Sub

呈现的代码上面的查找范围内使用了绝对地址，并且还避免使用autofill，因为在这种情况下不需要（在此上下文中都可以很容易地写入相同的公式为垂直范围的单元格，就像单个单元格）。