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 !

 

[Trebor76]

Hi Khellian,

Welcome to MrExcel!!

A couple of initial queries:

• What is the Column housing the formula
• What is the formula

Thanks,

Robert

 

[R Pelle]

If you have one of the latest Excel versions that has Tables you may want to consider converting your range in to a table. The formula(s) will automatically copy themselves down to the last row.

This may resolve your issue and there's no need for VBA.

Rob

 

[Peter_SSs]

Welcome to the MrExcel board!

Are the additional values each day being added manually or by code? Further to R Pelle's comment, earlier versions have an option to 'Extend data range formats and formulas' that may also be some use to you.

So we need to know your Excel version and a bit more detail about just what is happening with your sheet and where things are in it.

 

[Khellian]

Thanks for the response guy - sorry I should have been clearer. The version that I have is from Office 2010 so it should have the latest features.

To answer Robert's question, in actual fact there are two cells that I want to auto fill with different formulae for both but I assume that the method for one would be similar for both.

One is "F6=B6/E6/D6" and the other is "G6=DAYS360(A6,$K$2)". Hence by entering data on the 7th. row for the date (A7) then three integer cells (B7,E7 and D7) I am looking for a way to automatically populate F7 and G7. My first thoughts were to use some macros but if there is a more efficient way that would be great.

Thanks again - David

 

[Peter_SSs]

My first thoughts were to use some macros but if there is a more efficient way that would be great.

I think Rob has already given you a good suggestion:

If you have one of the latest Excel versions that has Tables you may want to consider converting your range in to a table. The formula(s) will automatically copy themselves down to the last row.

This may resolve your issue and there's no need for VBA.

Rob

If your data is something like mine below (not sure if you have something in column C or not) then

1. Select a cell in the table area

2. Go to the 'Insert' ribbon tab|Table|If Excel does not automatically select the current data area, then do so|ensure 'My table has headers' is checked|OK

A table should be created and as you enter data in the next row, the formulas should automatically populate into thet new row.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Int 1	Other	Int 2	Int 3	Division	Days
2	23/07/11	1		5	5	0.04	158				31/12/11
3	24/07/11	5		8	3	0.2083	157
4	25/07/11	6		6	6	0.1667	156
5	26/07/11	9		3	9	0.3333	155
6	27/07/11	3		5	8	0.075	154
7	28/07/11	5		6	4	0.2083	153
8	29/07/11	8		9	7	0.127	152
9
10
AutoFillFormulas

 

[Khellian]

Thanks for the instructions Peter - using them I created the table as you and Rob suggested. The format of the table that you showed in your reply is exactly the same as mine, however there must be something that I'm missing as the formulas are not in place when I add data into the next row. They still exist in the upper rows but the new row cell where the formula should be is blank. Is there a setting perhaps that I am missing somewhere?

thanks and regards

David

 

[Peter_SSs]

I created the table as you and Rob suggested.

At this point ..

1. Did you get alternating blue rows all the way to the last row of existing data/formulas - and no further?

2. Were the formulas in columns F & G of that last row - and no further?

.. the formulas are not in place when I add data into the next row. id

Can you confirm that the "next row" you are reeferring to here was the first white row below the coloured table rows?

 

[R Pelle]

Have a look at this example. Sometimes it's best to try out on a simple table before applying it to your own data just to get a feel for it.
As long as your "new data" is held within the table you should be fine.

Read Down and then Across



Rob.

 

[Khellian]

Thanks once again guys - I'm indebted to you all. I experimented with using tables and I think that that is the way to go. I'm experimenting with a few options that will make the formatting a little closer to what I was looking for. I recreated everything from scratch and now the table works as expected. 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. Either way, this accomplishes what I was looking to do.

Many thanks again I appreciate your advice. !