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?

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.


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


Nuraq wrote in

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

Declaring variables, this works he
----------------------
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
----------------------

"keepITcool" wrote:


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.


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


Nuraq wrote in

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

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
| www.XLsupport.com | keepITcool chello nl | amsterdam


JLXL wrote in

Dim MyRange As Excel.Range
Set MyRange = Worksheets("DecisionScores").Range(Cells(3, 2),
Cells(3, 6))

hmm..
Dim MyRange as Range line has disappeared :(

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


keepITcool wrote in
ft.com


Cells used without a qualifier refer to the activesheet

Function CriteriaScore2() As Long

Dim MyRange as Range

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.

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


--
HTHs Martin


"Nuraq" wrote:

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?