Dynamic AutoFill

[datauser]

Hello!

I need some help figuring out what I am missing here. I am trying to autofill a selection down the rows where the starting point of my autofill is dynamic and the ending point of my autofill is also dynamic.

Dim lastRowB As Long

lastRowB = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row

ws.Cells(lastRowB + 4, 2).Formula2 = "=UNIQUE(TOCOL(Query2!C:C,1))"
ws.Cells(lastRowB + 4, 3).Formula2 = "=TRANSPOSE( UNIQUE(Query2[ID]))"

WDate = ws.Cells(lastRowB + 5, 2).Address(False, True) This is the "Week Beginning Dates"
PB = ws.Cells(lastRowB + 4, 3).Address(True, False) This is the "25530"

'ws.Cells(lastRowB + 5, 3).Formula2 = "= " & PB & ""
ws.Cells(lastRowB + 5, 3).Formula2 = "=SUMIFS(Query2[Total],Query2[ID]," & PB & ",Query2[''Dates'']," & WDate & ")"
StartCell = ws.Cells(lastRowB + 5, 3).Address
StartCell.AutoFill Range("" & StartCell & " :C" & Range("B" & Rows.Count).End(xlUp).Row)

Any Help is appreciated!

 

[Kaper]

It's not clear how your data is organized (sheet Query2 with table Query2 in it? amd one of columns called 'Dates', etc., what is ws sheet, etc.

But the clearly visible problem for me is using string, not range for autofill.
try for 2 last lines of code:

Set StartCell = ws.Cells(lastRowB + 5, 3)
StartCell.AutoFill Range("" & StartCell.Address & " :C" & Range("B" & Rows.Count).End(xlUp).Row)

if you have above a definition of StartCell, change it to range type:

dim StartCell as range

 

[MARK858]

Just a different way of writing it without .Address (please note the ws in red)

Set StartCell = ws.Cells(lastRowB + 5, 3)
StartCell.AutoFill Range(StartCell, ws.Range("C" & ws.Range("B" & Rows.Count).End(xlUp).Row))

StartCell should be declared as Range and ws as WorkSheet