CODEWITHSUNDEEP
excelinsertvlookupvba
Jonh
Jonh

Reputation: 111

Inserting VBA formula into a defined Range

I'm looping through a table (Sheet4) in a worksheet and inserting an empty column between each original column. In the new, empty columns, I want to insert a VLookup function.

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

I'm having problems with the VLookup. I don't know if it's the way I'm storing my variables or if I need to have a paste function after my Vlookup. Can someone take a look and give me a hand? *note - I stored FormulaRange as String becuase As Range wouldn't let me pass my code into the variable. As a result I can't use the FormulaRange.Formula Method.

If I were to manually input the VLookup I would write it as =VLOOKUP(B1,RosettaStone_Employees!$A$1:$B$5,2,FALSE) and then copy down 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

Upvotes: 4

Views: 3030

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

I changed your code a little to get rid of the sheet activates. Also I changed a few things to ranges and included with blocks.

This is untested:

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

The R1C1 is a relative nomenclature. When it fills the formulas into the columns it will return the cell relative to the one into which the formula will be filled.

For example, above I use R1C[-1]. This says get the first row of the column directly to the left. So if the formula was being entered into B2 it would return A$1.

The [] denotes the relative address. Without the [] eg R1C1 it would indicate an absolute address and would return $A$1. So R1C1:R4C2 would return a range of $A$1:$B$4.

Upvotes: 1

Related Questions