Insert a VBA formula into a specific range

Question

I look at a table (Sheet4) on a sheet and insert an empty column between each source column. In the new empty columns, I want to insert the VLookup function.

I successfully inserted the columns, and I successfully held the correct range (the correct number of rows too) in a variable called FormulaRange.

I have problems with VLookup. I don’t know if this is how I store my variables or if I need to have an insert function after my Vlookup. Can someone take a look and give me a hand? * note - I saved FormulaRange as a String because Range did not allow me to pass my code to a variable. As a result, I cannot use the FormulaRange.Formula method.

If I manually entered VLookup, I would write it as = VLOOKUP (B1, RosettaStone_Employees! $ A $ 1: $ B $ 5.2, FALSE), and then copied the range.

    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

thanks

Jonathan

Answer 1

I changed my code a bit to get rid of the sheet activation. I also changed a few things to ranges and included them in blocks.

This is not verified:

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- relative nomenclature. When he fills in the formulas in the columns, he returns the cell relative to the one into which the formula will be filled.

For example, I use above R1C[-1]. This indicates that the first row of the column is on the left. Therefore, if the formula was entered in B2, it will return A$1.

[] . [] eg R1C1 $A$1. , R1C1:R4C2 $A$1:$B$4.