Sum of a dynamic range in VBA, How to:

KniteMare

Board Regular
Joined
Mar 4, 2002
Messages
238
ActiveSheet.Range("E2").value = Sum(ActiveSheet.Range(Cells(st, colStart), Cells(dn, colStart)))

The above "Does Not Work", but should give you an idea of what I am trying to do.

What I want to do is get the total of this range during the code execution so that I can place it in Cell E2.

If I use the Formula Method I get a #NAME? error.

Yours in EXCELent Frustration

KniteMare

PS. I found a great Animated.GIF (I saved it in my Private Drive, as the author does not want hyperlinking to his site to utilize these.)I want to use in my signature, how do I go about placing it there? PM With answer as It would seem to be a bit outside the normal disscussion group topics. Tnx in advance Knite.
This message was edited by KniteMare on 2002-09-20 12:23
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Maybe something like this might help?

Range("E2").Value = WorksheetFunction.Sum(Range("A2", Range("A2").End(xlDown)))
 
Upvote 0
Tom, Thanks for the reply,
That is how I derived the variables that I use to define the range, viv a vis the Range(Cells(....

Like this:

colStart = 2
nm = ActiveSheet.Name
st = Cells(1, colStart).End(xlDown).Row
dn = Cells(200, colStart).End(xlUp).Row
Set myRange = ActiveSheet.Range _
(Cells(st, colStart), Cells(dn, colStart))

What I need to do is have Cell E2 = the sum of the target range above offset by 0,1

I messed with the Help files to no avail. I figured I'd post here before I try a different approach.

Yours in EXCELent Frustration

KniteMare
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,133
Members
453,642
Latest member
jefals

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