Hi.
I have column dates in A and row headers in 1.

The columns and rows change in size everyday.

I'm trying to auto-fill a formula in B2 to the variable size of my range below Row 1 headers and to the right of Column A dates.

So far, I have this, but I'm hitting errors. Does anyone one know I'm doing wrong?

'Auto fill data
Sheets("Sheet1").Select
Range("B2").Select
With Range.Select
    .FormulaR1C1 "=HLOOKUP($A2,Sheet2!$V$9:$XFD$120000,MATCH(B$1,Sheet2!$F$11:$F$300,0)+2,FALSE)"
    'find the # of rows in Column A
    .Resize(Cells(Rows.Count, "A").End(xlUp).Row - 1, 1).FillDown
End With
Sheets("Sheet1").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
'select the auto-filled formulas in Column B
With Range.Select
    'find the # of columns in Row 1
    .Resize(Cells(Columns.Count, "1").End(xlRight).Column - 1, 1).FillRight
End With
I keep getting the Compiler error, "Argument not optional"

Does any one know how to fix this?

I can attach an example if necessary.

Any help is greatly appreciated.

Thanks