Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in a Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in a Macro
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 !!;) -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=516449 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in a Macro
Tks Dave,
Regards Kaye On Sat, 25 Feb 2006 07:09:31 -0600, davesexcel wrote: 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 !!;) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Macro | Excel Discussion (Misc queries) | |||
Help With Formula and Macro Please | Excel Programming | |||
Macro/Formula Help? | Excel Discussion (Misc queries) | |||
Macro - formula for end of last row | Excel Worksheet Functions | |||
Macro - formula for end of last row | Excel Worksheet Functions |