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!

 

[datauser]

is this one any better?
View attachment 119453

 

[MARK858]

No it is still an image, you need to either install the forums XL2BB addin or upload a sanitized file to a free file hosting site like www.box.com or www.dropbox.com (if doing this remember to mark it for sharing and post the link it then provides in the thread).

Preferably install XL2BB

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[datauser]

WIP - Copy11.18.2024.xlsm
	A	B	C	D	E	F
10
11
12		'Week Beginning Dates'	25530	26989	28182	26457
13		1/22/2024	0.5	0	0	0
14		1/29/2024
15		2/5/2024
16		2/12/2024
17		2/19/2024
18		4/29/2024
19		5/6/2024
20		5/13/2024
21		5/20/2024
22		5/27/2024
23		6/3/2024
24		6/10/2024
25		6/17/2024
26		6/24/2024
27		7/1/2024
28		7/8/2024
29		7/15/2024
30		7/22/2024
31		7/29/2024
32		8/5/2024
33		8/12/2024
34		8/19/2024
35		8/26/2024
Budget Summary

Cell Formulas
Range	Formula
B12:B44	B12	=UNIQUE(TOCOL(Query2!C:C,1))
C12:F12	C12	=TRANSPOSE(UNIQUE(Query2[ProjectBudgetID]))
C13	C13	=SUMIFS(Query2[Total],Query2[ProjectBudgetID],$C12,Query2[''Week Beginning Dates''],$B13)
D13	D13	=SUMIFS(Query2[Project],Query2[''Team Members''],$C12,Query2[Total],$B13)
E13	E13	=SUMIFS(Query2[Updated],Query2[''Week Beginning Dates''],$C12,Query2[Project],$B13)
F13	F13	=SUMIFS(Query2[BudgetYear],Query2[Total],$C12,Query2[Updated],$B13)
Dynamic array formulas.

 

[MARK858]

You need to remove the $ from your column references in your formula

 

[MARK858]

Also in the code I posted it looks like

 lColrow = lastRowB + 4

should be just

lColrow = lastRowB

 

[datauser]

Also in the code I posted it looks like

 lColrow = lastRowB + 4

should be just

lColrow = lastRowB

I wasn't able to figure this one out, so I'm just resulting to a pivot table

 

[MARK858]

As long as it works for you go with it

 

[datauser]

As long as it works for you go with it

Thanks for all of your help! I really appreciate it!

 

[MARK858]

You're welcome