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

Set w=Worksheets("Sheet1")
Set myRange = w.Range(w.Cells(4, 3),w. Cells(4, 6))

--
Gary''s Student - gsnu200770

Thanks, that works. I guess it was a syntax error on my part.
--
charlie


"Gary''s Student" wrote:

Set w=Worksheets("Sheet1")
Set myRange = w.Range(w.Cells(4, 3),w. Cells(4, 6))

--
Gary''s Student - gsnu200770

Assuming that your data always begins in "C4". This should get what
you are looking for and avoid any problems with not having "Sheet1" as
the active sheet.

Sub SumRow()
Dim wb As Workbook
Dim wsCurr As Worksheet
Dim rStartCell As Range
Dim myRange As Range
Dim iValue As Integer

Set wb = Application.Workbooks("test.xls")
Set wsCurr = wb.Worksheets("Sheet1")
Set rStartCell = wsCurr.Range("C4")
Set myRange = wsCurr.Range(rStartCell, rStartCell.End(xlToRight))

iValue = Application.WorksheetFunction.Sum(myRange)

Range("A1").Value = iValue
End Sub

You will of course need to modify the workbook and sheet names.
Though I'm new to VBA and programming in general and there is probably
a better way, I would suggest always declaring your workbooks,
worksheets, and ranges in a manner similar to the above.


On Feb 27, 12:18*pm, charlie
wrote:
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