Formula in a Macro

[Kaye]

I have a budget spreadsheet with future pay dates for the next number
of years in row 1 (H1..BA1) across the top.

Down the left in col B (B2..B800) I have "Due Dates",and in col C
"Amount"

I have the formular =IF(AND($B2>=H$1,$B2<I$1),$C2,"-") copied to about
150,000 cells that calculates when an amount is due, and puts that
amount under the appropriate pay date.

By inserting this formular, the spreadsheet had blown out quickly from
700Kb to 3.8Mb.

Can the above calculation be achieved with a macro that would update
those 150,000 cells (H1..BA1000) with the click of a button?

Regards, Kaye

 

[davesexcel]

place your cell at the cell you want to copy,
hit the macro recorder and make sure you are in relative mode, that is
when the square button appears pressed, now copy the cell you are on go
to the cell below and paste the formula, now stop recording.
Go to the forms toolbox and put a button on your spreadsheet, assign
that macro to that button. Now everytime you press that button the
macro initiates and whereever your cell is activated it will copy and
paste to the cell below it,
this is an example of what you can do to achieve what you want.
Experiment, you can record the macro so it will find the last cell in
the row and then go one down, by being in relative mode in your
recorder, select your column and press end and down arrow then down
arrow again, now you should be on the first empty row, and if you had
copyied the formula now you can paste it on the first empty cell,
there are also many ways you can activate the macro besides a button,
you could use a worksheet change event, well there are all sorts !!

 

[Kaye]

Tks Dave,

Regards Kaye