Autofill for dynamic range

[Trevasaurus]

Hi. My problem seems simple but I'm having a lot of difficulty getting my Macro to work properly.

I have a sheet with a Column (A) of Dates and a Row (1) of Column Headers. Count of dates and Column headers change every day. I'm placing a lookup formula in B2 and I'd like it to do two things:
1. Auto fill down as far as the count of Column A (Range B:#)
2. Auto fill the new range in Column B2:#, to the right, as far as the Column Headers in Row 1.

This is what I have so far. It fills the code down to last row, but not to the right to last column.

Sub Auto_Fill()

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<code>
  .Resize(Cells(1, Columns.Count).End(xlToLeft).Column - 1).FillRight</code>
End With

End Sub

Any help is greatly appreciated.

 

[JoeMo]

Try adding a comma (in red below):

With Sheets("Sheet4").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
  .Resize(, Cells(1, Columns.Count).End(xlToLeft).Column - 1).FillRight
End With

 

[Trevasaurus]

Thanks Joe!

You're the man.