excel - 将 VBA 公式插入定义的范围

Question

我正在循环访问工作表中的表格 (Sheet4)，并在每个原始列之间插入一个空列。在新的空列中，我想插入一个 VLookup 函数。

我已成功插入列，并且已成功在名为 FormulaRange 的变量中保留正确的范围(也是正确的行数)。

我在使用 VLookup 时遇到问题。我不知道这是否是我存储变量的方式，或者我是否需要在 Vlookup 之后有一个粘贴函数。有人可以看一下并帮助我吗？*注意 - 我将 FormulaRange 存储为字符串，因为 As Range 不允许我将代码传递到变量中。因此，我无法使用 FormulaRange.Formula 方法。

如果我要手动输入 VLookup，我会将其写为 =VLOOKUP(B1,RosettaStone_Employees!$A$1:$B$5,2,FALSE)，然后复制范围。

    Sub InsertColumnsAndFormulasUntilEndOfProductivityTable()
    Dim ActivityRange As Range
    Dim UserName As String
    Dim FormulaRange As String
    Dim i As Integer
    Dim x As Long
    Dim y As Long
    Dim Startrow As String
    Dim Lastrow As String

    Sheet6.Activate
    Set ActivityRange = Range("A1", Range("B1").End(xlDown))
    Sheet4.Activate
    Range("A1").Select

    y = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row - 1
    x = (Sheet4.Cells(1, Columns.Count).End(xlToLeft).Column) * 2

    For i = 1 + 2 To x Step 2
        Columns(i).EntireColumn.Insert
            Startrow = 2
            Lastrow = y
            UserName = Cells(1, i - 1)
            FormulaRange = Cells(Startrow, i).Address & ":" & Cells(Lastrow + 1, i).Address
            FormulaRange = "=VLookup(UserName, ActivityRange, 2, False)"

    Next
End Sub

谢谢

乔纳森

Answer 1

我稍微改变了你的代码以摆脱工作表激活。此外，我还对范围进行了一些更改，并将其包含在 block 中。

这未经测试:

Sub InsertColumnsAndFormulasUntilEndOfProductivityTable()
Dim ActivityRange As Range
Dim UserName As String
Dim FormulaRange As Range
Dim i As Long
Dim x As Long
Dim y As Long
Dim Startrow As Long
Dim Lastrow As Long

With Sheet6
    Set ActivityRange = .Range("A1", .Range("B1").End(xlDown))
End With
With Sheet4
    y = .Cells(.Rows.Count, 1).End(xlUp).Row - 1
    x = (.Cells(1, .Columns.Count).End(xlToLeft).Column) * 2

    For i = 1 + 2 To x Step 2
        .Columns(i).EntireColumn.Insert
        Startrow = 2
        Lastrow = y
        UserName = .Cells(1, i - 1) 
        Set FormulaRange = .Range(.Cells(Startrow, i), .Cells(Lastrow + 1, i))
        FormulaRange.FormulaR1C1 = "=VLookup(R1C[-1],'" & ActivityRange.Parent.Name & "'!" & ActivityRange.Address(1, 1, xlR1C1) & ", 2, False)"
        'If you do not want dynamic formulas and just want the value 
        'then comment out the above and use the below.
        'FormulaRange.Value = Application.Vlookup(UserName,ActivityRange,2,False)
    Next
End With
End Sub

R1C1 是一个相对命名法。当它将公式填充到列中时，它将返回相对于要填充公式的单元格的单元格。

例如，上面我使用R1C[-1]。这表示将列的第一行直接放在左侧。因此，如果将公式输入到 B2 中，它将返回 A$1。

[]表示相对地址。如果没有[]，例如R1C1，它将指示绝对地址并返回$A$1。因此，R1C1:R4C2 将返回 $A$1:$B$4 范围。