Summing a moving range using VBA

IanSYP

New Member
Joined
May 19, 2005
Messages
9
Good Morning

I want to do something similar to the usual =sum(a1:b1) command but i keep getting #name returned.

Dim Topcel, Botcel as range

set topcel = activecell
set botcel =activecell.offset(5,0)
activecell.offset(6,0).select
activecell.formulaR1C1 = "=sum(topcel,botcel)"

Not too sure where i am going wrong
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try

Dim Topcel as string, Botcel as as string

topcel = activecell.address
botcel =activecell.offset(5,0).address
activecell.offset(6,0).select
activecell.formula = "=sum(" & topcel & ":" & botcell & ")"
 

IanSYP

New Member
Joined
May 19, 2005
Messages
9
Thank you that is great But:

When you pick up the cell address it has the dollar signs in it whereas i need it without
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Thank you that is great But:

When you pick up the cell address it has the dollar signs in it whereas i need it without

Then you need to set to row/column abusolute to false like

.address(0,0)
 

Forum statistics

Threads
1,141,094
Messages
5,704,306
Members
421,337
Latest member
DeuxMilleSangue

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top