Range object in Worksheetfunction.Sum

[Nuraq]

I am trying to return the sum of a range using a function "CriteriaScore".
Iam tryin to use the Cells object to specify the Range, but VB is not happy.
If I define the function as follows, it works fine:

Function CriteriaScore () As Integer
Set MyRange = Worksheets("DecisionScores").Range("B3:F3")
CiteriaScore = WorksheetFunction.Sum(MyRange)
End Function

However, as soon as I introduce the Cells reference, it doens't work:

Function CriteriaScore () As Integer
Set MyRange = Worksheets("DecisionScores").Range(Cells(3, 2), Cells(3,
6))
CiteriaScore = WorksheetFunction.Sum(MyRange)
End Function

Any ideas?

 

[keepITcool]

Cells used without a qualifier refer to the activesheet

Function CriteriaScore2() As Long
With Worksheets("DecisionScores")
Set MyRange = .Range(.Cells(3, 2), .Cells(3, 6))
End With
CriteriaScore2 = WorksheetFunction.Sum(MyRange)
End Function

I change the function's type to Long.
(or Double).. to avoid problems where the sum > 32k.

 

[Guest]

Declaring variables, this works here:
----------------------
Option Explicit

Public Function CriteriaScore() As Double
Dim MyRange As Excel.Range
Set MyRange = Worksheets("DecisionScores").Range(Cells(3, 2), Cells(3, 6))
CriteriaScore = WorksheetFunction.Sum(MyRange)
Set MyRange = Nothing
End Function
----------------------

 

[Guest]

Nuraq,

I think that you have misspelt criteriascore. Try using option explicit to
catch the error.

Otherwise you may not be referencing the cells correctly and explicitlty
reference the cells as in the code below:
.....
Worksheets("DecisionScores").Cells(3, 2)
....

This is my try:

Option Explicit

Function CriteriaScore() As Integer
Dim MyRange As Range
Set MyRange = Worksheets( _
"DecisionScores").Range(Worksheets("DecisionScores").Cells(3, 2), _
Worksheets("DecisionScores").Cells(3, 6))
CriteriaScore = WorksheetFunction.Sum(MyRange)
Set MyRange = Nothing
End Function

 

[keepITcool]

JLXL,

if you comment on my post, PLS read it carefully.
your code ONLY works IF DecisionScores is Active

Try following:

Dim MyRange As Range

'assume a fresh workbook (with >=2 sheets)

Worksheets(1).Name = "Test
Worksheets(2).Name = "DecisionScores"
Worksheets(2).Activate
'this works
Set MyRange = _
Worksheets("DecisionScores").Range(Cells(3, 2), Cells(3,6))


'This will give a runtime error
Worksheets(1).Activate
Set MyRange = _
Worksheets("DecisionScores").Range(Cells(3, 2), Cells(3,6))

 

[keepITcool]

hmm..
Dim MyRange as Range line has disappeared

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote in

Cells used without a qualifier refer to the activesheet

Function CriteriaScore2() As Long

Dim MyRange as Range