Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
Hi I have a defined cell range(called simon) which currently covers cell A35 to A40. The size of the range can expand / contract depending on whether rows ae inserted / deleted. The cell the range starts can also alter. My problem is where I insert a new role within the defined cell range as the formulas disappear. I need to re-instate these formulas prior to the spreadheet being closed. Therefore what I would like to do is run a macro which which will copy a forumula (probably stored inn cell A1) and copy and paste this to the each cell on the defined range, either it can paste formula in each cell, or if the macro is really clever it will only paste the formula in any empty cells within the range. Your help with this will be much appreciated. Regards Simon -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=526256 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
This is a crude, but I think it does what you asked. It has to be
placed under the ThisWorkbook object under the Micosoft Excel Objects folder: option explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cell As Range Range("'my worksheet'!A1").Copy For Each cell In Range("myrng").Cells If cell.Formula = "" Then cell.PasteSpecial Paste:=xlPasteFormulas End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
Sub AddFormula()
Dim rng as Range, cell as Range On error resume next set rng = range("Simon").SpecialCells(xlBlanks) On error goto 0 if not rng is nothing then for each cell in rng cell.formula = Range("A1").formula ' or Range("A1").Copy cell Next End if end Sub -- Regards, Tom Ogilvy "sgrech" wrote in message ... Hi I have a defined cell range(called simon) which currently covers cell A35 to A40. The size of the range can expand / contract depending on whether rows ae inserted / deleted. The cell the range starts can also alter. My problem is where I insert a new role within the defined cell range as the formulas disappear. I need to re-instate these formulas prior to the spreadheet being closed. Therefore what I would like to do is run a macro which which will copy a forumula (probably stored inn cell A1) and copy and paste this to the each cell on the defined range, either it can paste formula in each cell, or if the macro is really clever it will only paste the formula in any empty cells within the range. Your help with this will be much appreciated. Regards Simon -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=526256 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
Thanks Tom for your help, this has partly solved my problem. However when I run this Macro the previously empty cell within the range has a formula within it but the cell references copied are not relative i.e the formula is =IF(C1="","",($I$1-C1)) instead of =IF(C45="","",($I$1-C45)). How can I correct this. Also on a more general question how can I attach two macros to one macro button. Thanks in advance. Simon -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=526256 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
For the first question i'm not sure how this works out in your example.
You may need to use $C1 rather than C1 in the formula For the second question apllication.run followed by the name of the procedure I would then assign Macro3 to a button Sub Macro3() Application.Run "Book2!Macro1" Application.Run "Book2!Macro2" End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
that is why I offered two choices.
cell.formula = Range("A1").formula ' or Range("A1").Copy cell use the Range("A1").Copy cell choice to adjust the range as you describe. to call two macros with one button, use a third macro Sub btn_Click() MyMacro1 MyMacro2 End Sub Where MyMacro1 and MyMacro2 represent the names of the macros to be executed when the button is pressed. Link the button to btn_click macro (if this is a button from the forms toolbar or a menu button) If it is a commandbar button from the control toolbox toolbar, then just call them from the click event. Private Sub CommandButton1_Click() MyMacro1 MyMacro2 End Sub -- Regards, Tom Ogilvy "sgrech" wrote in message ... Thanks Tom for your help, this has partly solved my problem. However when I run this Macro the previously empty cell within the range has a formula within it but the cell references copied are not relative i.e the formula is =IF(C1="","",($I$1-C1)) instead of =IF(C45="","",($I$1-C45)). How can I correct this. Also on a more general question how can I attach two macros to one macro button. Thanks in advance. Simon -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=526256 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range - copy forumlas
Thanks Tom that's fantastic!! Your help is much appreciated. -- sgrech ------------------------------------------------------------------------ sgrech's Profile: http://www.excelforum.com/member.php...o&userid=14501 View this thread: http://www.excelforum.com/showthread...hreadid=526256 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forumlas for Dates | Charts and Charting in Excel | |||
Forumlas | Excel Discussion (Misc queries) | |||
Forumlas or scripts? | Excel Discussion (Misc queries) | |||
Forumlas Not Executing | Excel Discussion (Misc queries) | |||
Help with Commission forumlas | Excel Worksheet Functions |