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

 

[datauser]

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

This worked! How would I fill in my formula to the right?

 

[Joe4]

datauser,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.

 

[datauser]

datauser,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.

Sorry about that. I realized that I didn't mark the solution as best as I was responding to the solution. I didn't realize I was marking my response as the solution.

 

[MARK858]

This worked! How would I fill in my formula to the right?

Untested

    Dim lastRowB As Long
    Dim lColrow As Long, lCol 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]))"


    PB = ws.Cells(lastRowB + 4, 3).Address(True, False) 'This is the "25530"

    ws.Cells(lastRowB + 5, 3).Formula2 = "=SUMIFS(Query2[Total],Query2[ID]," & PB & ",Query2[''Dates'']," & WDate & ")"



    lColrow = lastRowB + 4
    lCol = ws.Cells(lColrow, Columns.Count).End(xlToLeft).Column

    Set StartCell = ws.Cells(lastRowB + 5, 3)
    StartCell.AutoFill Range(StartCell, ws.Cells(StartCell.Row, lCol))
    'or without autofill
    'Range(StartCell, ws.Cells(StartCell.Row, lCol)).Formula2 = "=SUMIFS(Query2[Total],Query2[ID]," & PB & ",Query2[''Dates'']," & WDate & ")"

Edit: Changed 3 to StartCell.Row just to make it more generic

 

[datauser]

Untested

    Dim lastRowB As Long
    Dim lColrow As Long, lCol 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]))"


    PB = ws.Cells(lastRowB + 4, 3).Address(True, False) 'This is the "25530"

    ws.Cells(lastRowB + 5, 3).Formula2 = "=SUMIFS(Query2[Total],Query2[ID]," & PB & ",Query2[''Dates'']," & WDate & ")"



    lColrow = lastRowB + 4
    lCol = ws.Cells(lColrow, Columns.Count).End(xlToLeft).Column

    Set StartCell = ws.Cells(lastRowB + 5, 3)
    StartCell.AutoFill Range(StartCell, ws.Cells(StartCell.Row, lCol))
    'or without autofill
    'Range(StartCell, ws.Cells(StartCell.Row, lCol)).Formula2 = "=SUMIFS(Query2[Total],Query2[ID]," & PB & ",Query2[''Dates'']," & WDate & ")"

Edit: Changed 3 to StartCell.Row just to make it generic

This did not work for me. I am able to fill my formula to the right using the macro below, but it only fills down the first column. Any suggestions?

Set ws = ThisWorkbook.Sheets("Budget Summary")

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(Query2ID]))"

ws.Cells(lastRowB + 5, 3).Formula2 = "=SUMIFS(Query2!$D:$D,Query2!$A:$A,C$12,Query2!$C:$C,$B13)"


Dim StartCell As Range
Set StartCell = ws.Cells(lastRowB + 5, 3)

Dim LastColumn As Long
LastColumn = ws.Cells(lastRowB + 4, ws.Columns.Count).End(xlToLeft).Column

'Count Columns
StartCell.AutoFill Range(StartCell, ws.Cells(lastRowB + 5, LastColumn))

'Counting Rows
StartCell.AutoFill Range(StartCell, ws.Range("C" & ws.Range("B" & Rows.Count).End(xlUp).Row))




End Sub

 

[MARK858]

Any suggestions?

Not without having a usable screenshot or a sample workbook rather than an image

 

[datauser]

Not without having a usable screenshot or a sample workbook rather than an image