I am having difficulty setting a Range so that I can use
Application.WorksheetFunction.Sum(myRange) to return a Sum of the values from
a range which I need to be a row of values. I don't know until runtime how
long this range will be thus I would like to define the Range using the Cells
reference.

If I use: Set myRange = Worksheets("Sheet1").Range("C4:F4")
it works fine.

If I use: Set myRange = Worksheets("Sheet1").Range(Cells(4, 3), Cells(4, 6))
I get a Run Time Error '1004' unless "Sheet1" is active. Test this code
when "Sheet2" is active to get this error.

Here is my code that I have used to illustrate these different behavors.

Dim myRange As Range
Dim iValue As Integer

'Worksheets("Sheet1").Activate
Set myRange = Worksheets("Sheet1").Range(Cells(4, 3), Cells(4, 6))
'Set myRange = Worksheets("Sheet1").Range("C12:F4")

iValue = Application.WorksheetFunction.Sum(myRange2)

End Sub
--
Thanks for any help on this error or a different approach I can take. I'm
using Excel 2003 (SP3) although I get the same behavior with Excel 2007.

charlie