VBA to copy/paste dynamic formula across range?

[trillium]

Hi
I'm trying to figure out code to copy and paste a set of formulas across a bunch of columns and then down to the end of the range. I can figure out that part with VBA, but I can't figure out how to set the formulas so that the cell references don't get messed up in VBA.

It's just a simple COUNTIFS and SUMIFS formulas but the column references need to stay and some sell references need to move either right or down.

=COUNTIFS('Order Export'!$B:$B,'Customer List'!$A2,'Order Export'!$L:$L,'Customer List'!K$1)

So that when the macro moves the formula over to cover 5 columns, the $B:$B/$L:$L stay the same but A2 will also stay the same for the row, but change to A3 in the next and K1 will become L1, M1, N1,O1... and next year when 2019 data is entered the new column P will have the formula...

I want to use some type of autofill with dynamic range script to move the formulas and the corresponding references over.

Any thoughts??

 

[footoo]

Paste to the whole range at once in one line, for example :

[A1:D10].Formula="=COUNTIFS('Order Export'!$B:$B,'Customer List'!$A2,'Order Export'!$L:$L,'Customer List'!K$1)"

 

[trillium]

That's brilliant!

Can I ask, how to do make this part of the code dynamic? [A1:D10]?

In my case it's K2:P2 and then R2:W2 with totals in columns Q & X. But next year there will be another year's worth of data, so the formulas will be pasted into K2:Q2, R2:W2 with totals in columns R&Y.

I tried a few things to make that part dynamic as well but not working.

 

[footoo]

For columns K:P and R:W from row 2 to the last data row (using column A to determine the last row) :

Dim lr&, rng As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("K2:P" & lr & ",R2:W" & lr)
rng.Formula = "=COUNTIFS('Order Export'!$B:$B,'Customer List'!$A2,'Order Export'!$L:$L,'Customer List'!K$1)"

And similarly for your Q & X formulas.

 

[trillium]

Thanks - I'm good with finding the last row and changing the variable there to make the formula extend farther down. What I can't figure out is how to tell Excel to copy the formula over one column further. How do I make "K" and "P" dynamic so that next year when another year of data is added it pastes the countif formula from K to Q?

 

[trillium]

I think I figured it out!

Range(Cells(2, 11), Cells(LastRow, lastcol)).Select