CODEWITHSUNDEEP
excelvba
natbuild34
natbuild34

Reputation: 21

Changing a Desired Range

I am a novice to vba and would really value direction on getting around this current road block. I would like to run a Vlookup for a selected range. The Vlookup will reference data that is updated every day.

  1. Day 1, I would like for the Vlookup to return a value in each cell of the selected Range ("B2:B4").
  2. Day 2, the referenced data will change and I would like for the selected range to move one column to the right Range("C2:C4").

I would like to make sure each time I run the macro, the range moves one column to the right in order to compare the change in data from one day to the next.

I have provided the code below, which allows me to run a Vlookup in Range("B2:B4") but I have yet to figure out how to change the range based upon the next empty column.

That said, thank you very much for the help, which is very much appreciated. 

Sub FillinBlanks()
    Dim rng As Range

    Set rng = Worksheets("Sheet2").Range("b2:b4")

    For Each cell In rng
        If IsEmpty(cell) Then
            cell.FormulaR1C1 = "=vlookup(Sheet2!rc1,Sheet1!r5c6:r1000c8,3,false)"
        End If
    Next cell
End Sub

Upvotes: 2

Views: 80

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

What you can do is find the last column in row 2 and simple add 1 to it. I am assuming that row 1 has headers and hence I am trying to find last column in row 2.

See this example.

Sub FillinBlanks()
    Dim rng As Range, cell As Range
    Dim lCol As Long

    With Worksheets("Sheet2")
        '~~> Find the last column in row 2
        lCol = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1

        '~~> Construct your range
        Set rng = .Range(ReturnName(lCol) & "2:" & ReturnName(lCol) & "4")
    End With

    For Each cell In rng
        If IsEmpty(cell) Then
            cell.FormulaR1C1 = "=vlookup(Sheet2!rc1,Sheet1!r5c6:r1000c8,3,false)"
        End If
    Next cell
End Sub

'~~> Function to return column name from column number
Function ReturnName(ByVal num As Integer) As String
    ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

Upvotes: 2

Related Questions