Range - copy forumlas

[sgrech]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[sgrech]

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

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[sgrech]

Thanks Tom that's fantastic!!

Your help is much appreciated.