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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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 & ")"
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top