PDA

View Full Version : Summing all rows in a column in a specified worksheet



NukedWhale
01-23-2009, 01:24 PM
I need to sum all the rows, not including the header row, in a column specified by the user for a specific worksheet.

The number of rows is MyRows
The user specified column is WeightingRange. WeightingRange is a string and the user entered in the column letter as text. I.e WeightingRange=G.
The worksheet is SourceSheet

I found a website that recommended using excel's built in functions.

This is an example they gave:
MySum = Application.WorksheetFunction.Sum(Range("A1:A100"))

How would I adapt this for what I want to do? This is how far I am. But I don't know how to make it refer to a specific worksheet, or if it would even work.
MySum = Application.WorksheetFunction.Sum(Range("&Weightingrange & "2:" & WeightingRange & str(MyRows)&"))

Bob Phillips
01-23-2009, 02:57 PM
MySum = Application.WorksheetFunction.Sum(Range(WeightRange & 2).Resize(MyRows))

NukedWhale
01-23-2009, 03:35 PM
That looks much simpler and digestible then the code I spewed. Would the line of code I added make sure that the calculation is performed on my sourcesheet?

Sheets(SourceSheet).Activate
MySum = Application.WorksheetFunction.Sum(Range(WeightRange & 2).Resize(MyRows))

NukedWhale
01-23-2009, 03:52 PM
Dang, this line of code gets a type mismatch error

Sheets(SourceSheet).Activate

Bob Phillips
01-23-2009, 03:58 PM
Maybe?



SourceSheet.Activate

NukedWhale
01-23-2009, 04:01 PM
That worked.

This line gave me an error
MySum = Application.WorksheetFunction.Sum(Range(WeightRange & 2).Resize(MyRows))

Run-time error '1004'
Method 'Range' of object '_Global' failed

Any ideas?

Bob Phillips
01-23-2009, 04:27 PM
What's in WeightedRange and in MyRows?

NukedWhale
01-23-2009, 05:19 PM
WeightingRange is a column in my source sheet (SourceSheet) that has numeric values 0-10 that I use to weight my results.

The sum of the values in my WeightingRange is what I'll use as a base size to calculate some percentages.

Does this help at all? Let me know if you need any further clarification. You've helped me out a ton over the past week. I really really appreciate everything you've helped me with so far.

Here's a few snapshots of code.


Dim WeightingRange As String
Dim MyRows As Long

'Prompt for Weighting Range
WeightingRange = InputBox("Please enter the column letter of the column used for weighting")
'Count the number of rows in the worksheet
MyRows = SourceSheet.UsedRange.Rows.Count