AutoFill Function to Match Range created by Column & Row

**Trevasaurus** · 07-09-2013, 10:28 AM

Hi.

This is a continuation of another post I made with an ambiguous title.

To elaborate on my problem, I have a Column ("A") of dates and a Row ("1") of Titles.
I'm using the following function to draw data from another sheet to populate my range based on dates and titles.

=HLOOKUP($A2,VAR!$V$9:$XFD$120000,MATCH(B$1,VAR!$F$11:$F$3000,0)+2,FALSE)

My approach is to Fill this formula down, based on the count of Column A.
My problem is getting the formula to fill right, based on the Count of Row 1.
The macro I have so Far, (with a partial fix from @Norie, via my last flagged post) is below.

Sub Fill_Data2()

Dim LastR As Long
LastR = Range("A" & Rows.Count).End(xlUp).Row

'this autofills my formula down "B" to the count of dates in "A"
With Sheets("Sheet1").Range("B2")
    .Formula = "=HLOOKUP($A2,Sheet2!$V$9:$XFD$120000,MATCH(B$1,Sheet2!$F$11:$F$3000,0)+2,FALSE)"
    .Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1, 1).FillDown
End With

Dim rng As Range
Dim c As String
Dim r As Integer

    LastR = Range("A" & Rows.Count).End(xlUp).Row

    c = "B"

    r = 2
    Set rng = Range(c & r)

    For r = 2 To LastR

        With rng
            .Resize(, Cells(1, Columns.Count).End(xlToLeft).Column - 1).FillRight
        End With

    Next r

End Sub

This Macro works for filling the function down "B" but it is only filling the formula in Column "B" to the right in the first row (Row 2).
I'm trying to get it to fill to the right for every row that "B" has a formula in.

Any help is greatly appreciated.

Thanks

**Norie** · 07-09-2013, 11:09 AM

Try this.

Dim rng As Range
Dim LastRow As Long
Dim LastCol As Long

    With Sheets("Sheet1")

       LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
       LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
 
       Set rng = .Range("B2", .Cells(LastRow, LastCol))

       rng.Formula = "=HLOOKUP($A2,Sheet2!$V$9:$XFD$120000,MATCH(B$1,Sheet2!$F$11:$F$3000,0)+2,FALSE)"

     End With