Macro Autofill

[Khellian]

Being new to Excel Macros I would appreciate some help in a small programming problem. I have a spreadsheet where I will be adding data on a daily basis. There is a date column with three additional columns where integers will be added, and a last column which will contain a formula that will calculate a value based on the three integers.

As each additional row will be new each day, I need to get a macro in place where by clicking on a button, the macro will find the last row in the formula column and then autofill down to the row below so that the formula is preserved and produces a result based on the new figures for that day.

I would greatly appreciate any advice. - Thanks in advance !

 

[Peter_SSs]

However, when I created a number of additional rows at the bottom of the table that would allow me to add data without having to increase the size of the table, I found that the formula in the empty lines showed a divide by zero error. I got round that by using conditional formatting which when an error is displayed turns the color of the text to the same as the background - hence it is invisible. Another way would be to just increase the size of the table by dragging it down a row each time I put data in.

Conditional Formatting is volatile and hence adds some overhead to your sheet. Whilst that is not likely to be a problem unless your sheet in very large, yet another way to try is to change your formulas slightly to eliminate the errors. For example (for row 2 copied down) try ..

=IFERROR(B2/E2/D2,"")
=IF(A2="","",DAYS360(A2,$K$2))

.. and remove the Conditional Formatting.

 

[R Pelle]

A way around that is as follows

Then you don't need Cond Formatting and if you need to Sum Column C at the bottom you won't have any issues either.

Excel Workbook
	A	B	C	D
1	A	B	C=B/A	ISERROR
2	0	1	#DIV/0!
3	1	2	2	2
4	2	8	4	4
5	0	1	#DIV/0!
6		Sum:	#DIV/0!	6
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C2	=B2/A2
C3	=B3/A3
C4	=B4/A4
C5	=B5/A5
C6	=SUM(C2:C5)
D2	=IF(ISERROR(B2/A2),"",B2/A2)
D3	=IF(ISERROR(B3/A3),"",B3/A3)
D4	=IF(ISERROR(B4/A4),"",B4/A4)
D5	=IF(ISERROR(B5/A5),"",B5/A5)
D6	=SUM(D2:D5)

EDIT: Hadn't seen Peter's reply yet.

 

[Khellian]

That's a much better way of doing it.