visual basic macro for excel....help!

visual basic macro for excel....help!

Post by dthoms » Wed, 24 Apr 1996 04:00:00



  I am writing a VBasic macro for excel spreadsheet.I have a report which
is divided into sections with variable #'s of rows.How can i sum each
column at the end of each section.The VBA formulaR1C1 property does not
accept variables.I need to have the formula entered into the cell so it
will recalculate if i change or add a row.This is the line i wanted to
enter : activecell.formulaR1C1="=sum(R[-Lines]C:R[-1]C)"
where Lines=# OF lines in the section.

                    TIA         N.G.

 
 
 

visual basic macro for excel....help!

Post by Ole P. Erlands » Wed, 24 Apr 1996 04:00:00



>  I am writing a VBasic macro for excel spreadsheet.I have a report which
>is divided into sections with variable #'s of rows.How can i sum each
>column at the end of each section.The VBA formulaR1C1 property does not
>accept variables.I need to have the formula entered into the cell so it
>will recalculate if i change or add a row.This is the line i wanted to
>enter : activecell.formulaR1C1="=sum(R[-Lines]C:R[-1]C)"
>where Lines=# OF lines in the section.

>                    TIA             N.G.

This would help :

ActiveCell.FormulaR1C1 = "=sum(R[-" & RN & "]C:R[-1]C)"

 RN is an integer variable containing your preferred number of rows.

Ole P. Erlandsen


 
 
 

visual basic macro for excel....help!

Post by Nachum Gin » Fri, 26 Apr 1996 04:00:00



>  I am writing a VBasic macro for excel spreadsheet.I have a report which
>is divided into sections with variable #'s of rows.How can i sum each
>column at the end of each section.The VBA formulaR1C1 property does not
>accept variables.I need to have the formula entered into the cell so it
>will recalculate if i change or add a row.This is the line i wanted to
>enter : activecell.formulaR1C1="=sum(R[-Lines]C:R[-1]C)"
>where Lines=# OF lines in the section.

>                    TIA             N.G.

Hi
Like this:

Sub SumIt()
Application.Goto reference:="r5c5" 'First cell in the row
x = Range(ActiveCell, ActiveCell.End(xlDown)).Count

ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 =  _
"=sum(R[-" & x & "]C:R[-1]C)"
End Sub

Good Luck
 Nachum Ginat    

     _/      _/    _/_/_/_/    _/_/_/_/
    _/_/    _/   _/           _/
   _/  _/  _/   _/   _/_/     _/_/_/
  _/    _/_/   _/      _/          _/
 _/      _/     _/_/_/_/   _/_/_/_/
http://www2.kalanit.co.il/nachum

 
 
 

visual basic macro for excel....help!

Post by Johan Skug » Mon, 29 Apr 1996 04:00:00




>>  I am writing a VBasic macro for excel spreadsheet.I have a report which
>>is divided into sections with variable #'s of rows.How can i sum each
>>column at the end of each section.The VBA formulaR1C1 property does not
>>accept variables.I need to have the formula entered into the cell so it
>>will recalculate if i change or add a row.This is the line i wanted to
>>enter : activecell.formulaR1C1="=sum(R[-Lines]C:R[-1]C)"
>>where Lines=# OF lines in the section.

>>                    TIA         N.G.
>Hi
>Like this:
>Sub SumIt()
>Application.Goto reference:="r5c5" 'First cell in the row
>x = Range(ActiveCell, ActiveCell.End(xlDown)).Count

>ActiveCell.End(xlDown).Offset(1, 0).FormulaR1C1 =  _
>"=sum(R[-" & x & "]C:R[-1]C)"
>End Sub
>Good Luck
> Nachum Ginat        

You can also try this:

  activecell.formulaR1C1="=sum(R["&-Lines &"]C:R[-1]C)"

(Adapted from your eample)

Hope it helps

/Johan Skugge
--

     (o o)      | phone: (int) +46-8-7149556 (nat) 08-7149556
-oOO--(_)--OOo- | snail: Johan Skugge; Bohusg 43, 9; 116 67 Sthlm; Sweden