Macro to autofill formula down many ranges

[bdd12]

I am trying to create a macro that will autofill three columns worth of formulas until the end of that range and then repeat those formulas for each range below it. For context, the file is a compilation of thousands of different series that could have as little as one line, or as many as dozens. Each series is split up by one blank row, so if the first range is from A1:U7, the next range would start in A9 and go to U(x), with x being whatever row the next block ends on, an so on. I have copied and pasted the code I have written so far, just need to find a way to autofill these formulas just until the end of the current range, and then loop that so it will run the same formulas for all the ranges below that are separated by one blank row

ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-22]),MONTH(RC[-22]),DAY(RC[-22]))"
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-18])=FALSE,IF(RC[-1]<RC[-18],RC[-1],""DONE""),RC[-1])"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=RC[-8],RC[-1],""ERROR"")"
Range("AA3").Select
ActiveCell.FormulaR1C1 = _
"=DATE(YEAR(R[-1]C),MONTH(R[-1]C)+R[-1]C[-20],DAY(R[-1]C))"
Range("AB3").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(R[-1]C[-18])=FALSE,IF(R[-1]C[-1]<R[-1]C[-18],R[-1]C[-1],""DONE""),R[-1]C[-1])"
Range("AC3").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-1]=R[-1]C[-8],R[-1]C[-1],""ERROR"")"
Columns("AA:AC").Select
Selection.NumberFormat = "m/d/yy"

 

[Joe4]

Welcome to the Board!

In determining which rows to apply this to, I assume that you have at least one column that will ALWAYS have data in it for any row with data. Which column is that, exactly?
And which columns exactly are you populating (in your first line, ActiveCell does not tell us where you are starting from)?

 

[bdd12]

Thank you! Yes, there are multiple columns that always have data when they aren't the blank row, one of them is column G. The first cell being populated is AA2, it goes across to AC2, and will need to go down as far as the last block of cells with data in it, which will change every time the report is pulled

 

[Joe4]

The cool thing is that you usually do not need to select a range in order to work with it (and it is usually faster and more efficent not to).
See if this does what you want:

Sub MyMacro()

    Dim lr As Long
    Dim rng As Range
    
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
    
'   Set range equal to all non-blank cells in column G
    Set rng = Range("G2:G" & lr).SpecialCells(xlCellTypeConstants, 23)
    
'   Set formula for column AA
    rng.Offset(0, 20).FormulaR1C1 = "=DATE(YEAR(RC[-22]),MONTH(RC[-22]),DAY(RC[-22]))"
    
'   Set formula for column AB
    rng.Offset(0, 21).FormulaR1C1 = "=IF(ISBLANK(RC[-18])=FALSE,IF(RC[-1]<RC[-18],RC[-1],""DONE""),RC[-1])"
    
'   Set formula for column AC
    rng.Offset(0, 22).FormulaR1C1 = "=IF(R[-1]C[-1]=R[-1]C[-8],R[-1]C[-1],""ERROR"")"
    
'   Set number format
    Columns("AA:AC").NumberFormat = "m/d/yy"
    
End Sub

 

[bdd12]

The cool thing is that you usually do not need to select a range in order to work with it (and it is usually faster and more efficent not to).
See if this does what you want:

Sub MyMacro()

    Dim lr As Long
    Dim rng As Range
   
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
   
'   Set range equal to all non-blank cells in column G
    Set rng = Range("G2:G" & lr).SpecialCells(xlCellTypeConstants, 23)
   
'   Set formula for column AA
    rng.Offset(0, 20).FormulaR1C1 = "=DATE(YEAR(RC[-22]),MONTH(RC[-22]),DAY(RC[-22]))"
   
'   Set formula for column AB
    rng.Offset(0, 21).FormulaR1C1 = "=IF(ISBLANK(RC[-18])=FALSE,IF(RC[-1]<RC[-18],RC[-1],""DONE""),RC[-1])"
   
'   Set formula for column AC
    rng.Offset(0, 22).FormulaR1C1 = "=IF(R[-1]C[-1]=R[-1]C[-8],R[-1]C[-1],""ERROR"")"
   
'   Set number format
    Columns("AA:AC").NumberFormat = "m/d/yy"
   
End Sub

Awesome! That worked in that it was able to create formulas in the correct cells for the entire worksheet, there is just a slight problem in that in column AA there are actually two different formulas. With how the export is set up the first line in each block needs to reference the corresponding cell in column E, but all lines below need to reference the cell directly above them, see the difference between:

ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-22]),MONTH(RC[-22]),DAY(RC[-22]))" (which is for the first line in each data block in column AA) and:
ActiveCell.FormulaR1C1 = "=DATE(YEAR(R[-1]C),MONTH(R[-1]C)+R[-1]C[-20],DAY(R[-1]C))" (which is for all lines below it)

Is there a way to do that?

 

[Joe4]

I think you need to show us what this data structure looks like, so it is clear to us what formulas need to go where.
It is hard for us to determine when we really have no idea what it all looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[bdd12]

grantSeriesReport.2022-04-18.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1			Amount		Start Date	Recurrence Rule	Frequency	Unit	Updated Frequency	End Date	Total Times	Payment Type		Total Expected Instances	Total Instances	Difference	Action	Value	Note	Expected Instance Date	Existing Instance Scheduled Date	Same Date?			Existing Instance Amount	Existing Instance Status
2			-100		3/1/22	every 3 months for 6 times	3	monthly	3		6			1	1	0				3/1/22	3/1/22	TRUE			-100	COMPLETE
3
4			-1900		6/17/21	every month	1	monthly	1					12	11	1				6/17/21	6/17/21	TRUE			-1900	COMPLETE
5			-1900		6/17/21	every month	1	monthly	1					12	11	1				7/17/21	7/17/21	TRUE			-1900	COMPLETE
6			-1900		6/17/21	every month	1	monthly	1					12	11	1				8/17/21	8/17/21	TRUE			-1900	COMPLETE
7			-1900		6/17/21	every month	1	monthly	1					12	11	1				9/17/21	9/17/21	TRUE			-1900	COMPLETE
8			-1900		6/17/21	every month	1	monthly	1					12	11	1				10/17/21	10/17/21	TRUE			-1900	COMPLETE
9			-1900		6/17/21	every month	1	monthly	1					12	11	1				11/17/21	11/17/21	TRUE			-1900	COMPLETE
10			-1900		6/17/21	every month	1	monthly	1					12	11	1				12/17/21	12/17/21	TRUE			-1900	COMPLETE
11			-1900		6/17/21	every month	1	monthly	1					12	11	1				1/17/22	1/17/22	TRUE			-1900	COMPLETE
12			-1900		6/17/21	every month	1	monthly	1					12	11	1				2/17/22	2/17/22	TRUE			-1900	COMPLETE
13			-1900		6/17/21	every month	1	monthly	1					12	11	1				3/17/22	3/17/22	TRUE			-1900	COMPLETE
14			-1900		6/17/21	every month	1	monthly	1					12	11	1				4/17/22	4/17/22	TRUE			-1900	APPROVED
15			-1900		6/17/21	every month	1	monthly	1					12	11	1				5/17/22
16
17			-500		5/2/22	every month	1	monthly	1					1	1	0				5/2/22	5/2/22	TRUE			-500	SUBMITTED
18
19			-400		7/1/21	every month	1	monthly	1					11	12	-1				7/1/21	6/7/21	FALSE			-400	COMPLETE
20			-400		7/1/21	every month	1	monthly	1					11	12	-1				8/1/21	7/1/21	FALSE			-400	COMPLETE
21			-400		7/1/21	every month	1	monthly	1					11	12	-1				9/1/21	8/1/21	FALSE			-400	COMPLETE
22			-400		7/1/21	every month	1	monthly	1					11	12	-1				10/1/21	9/1/21	FALSE			-400	COMPLETE
23			-400		7/1/21	every month	1	monthly	1					11	12	-1				11/1/21	10/1/21	FALSE			-400	COMPLETE
24			-400		7/1/21	every month	1	monthly	1					11	12	-1				12/1/21	11/1/21	FALSE			-400	COMPLETE
25			-400		7/1/21	every month	1	monthly	1					11	12	-1				1/1/22	12/1/21	FALSE			-400	COMPLETE
26			-400		7/1/21	every month	1	monthly	1					11	12	-1				2/1/22	1/1/22	FALSE			-400	COMPLETE
27			-400		7/1/21	every month	1	monthly	1					11	12	-1				3/1/22	2/1/22	FALSE			-400	COMPLETE
28			-400		7/1/21	every month	1	monthly	1					11	12	-1				4/1/22	3/1/22	FALSE			-400	COMPLETE
29			-400		7/1/21	every month	1	monthly	1					11	12	-1				5/1/22	4/1/22	FALSE			-400	PAID
30			-400		7/1/21	every month	1	monthly	1					11	12	-1					5/1/22				-400	SUBMITTED
31
32			-300		1/16/22	every month until March 6, 2022	1	monthly	1	3/6/22				2	4	-2				1/16/22	1/16/22	TRUE			-580	COMPLETE
grantSeriesReport.2022-04-18

 

[bdd12]

Hopefully that worked, I deleted some columns of irrelevant data that is specific to my company and left the ones I need for the formula. As you can see in column E, that just shows the start date and so in order to go one month ahead in the formulas I created, I can't reference that column every time. If there is a better way of setting up the formula I am open to it, but how I currently have it the first row needs to reference column E, but all others need to reference the cell directly above them

 

[Joe4]

I think rather than trying to figure out two different formulas and when to use which, it is better to create one long formula with an IF statement, to determine when to use each one.
Try this:

Sub MyMacro()

    Dim lr As Long
    Dim rng As Range
    
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
    
'   Set range equal to all non-blank cells in column G
    Set rng = Range("G2:G" & lr).SpecialCells(xlCellTypeConstants, 23)
    
'   Set formula for column AA
    rng.Offset(0, 20).FormulaR1C1 = _
        "=IF(ISNUMBER(R[-1]C[-22]),DATE(YEAR(R[-1]C),MONTH(R[-1]C)+R[-1]C[-20],DAY(R[-1]C)),DATE(YEAR(RC[-22]),MONTH(RC[-22]),DAY(RC[-22])))"
    
'   Set formula for column AB
    rng.Offset(0, 21).FormulaR1C1 = "=IF(ISBLANK(RC[-18])=FALSE,IF(RC[-1]<RC[-18],RC[-1],""DONE""),RC[-1])"
    
'   Set formula for column AC
    rng.Offset(0, 22).FormulaR1C1 = "=IF(R[-1]C[-1]=R[-1]C[-8],R[-1]C[-1],""ERROR"")"
    
'   Set number format
    Columns("AA:AC").NumberFormat = "m/d/yy"
    
End Sub