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

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

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

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

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

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

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