VBA to SUM a row of numbers
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 |
VBA to SUM a row of numbers
Set w=Worksheets("Sheet1")
Set myRange = w.Range(w.Cells(4, 3),w. Cells(4, 6)) -- Gary''s Student - gsnu200770 |
VBA to SUM a row of numbers
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 |
VBA to SUM a row of numbers
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 |
All times are GMT +1. The time now is 11:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com