using the cells property for use with Application.WorksheetFunction.Sum

[EAB1977]

Has anyone had any success with using the Cells Propery when using the
Application.WorksheetFunction.Sum function? My code is below:

Sub AddDataMorningMil(mon As String, Day As String, yr As String,
intDOW As Integer) 'DOW = Day of Week
Dim x As Integer, row As Integer, colStart As Integer, colEnd As
Integer, varSum As Variant

' -- Friday column & starting row
colEnd = 6
row = 15

' -- Get the start column & row
Select Case intDOW
Case 2 'Monday
colStart = 2
Case 3 'TuesDay
colStart = 3
Case 4 'WednesDay
colStart = 4
Case 5 'ThursDay
colStart = 5
Case 6 'FriDay
colStart = 6
Case Else
colStart = 2
End Select

Sheets("Monthly_Summary").Select
x = 1

Do
If Cells(row, colStart).value <> "" Then
col = col + 1
Else
Exit Do
End If
Loop

Sheets(mon & "_" & Day & "_" & yr).Select
Cells(row, colStart).Select
varSum = Application.WorksheetFunction.Sum(Range(Cells(row,
colStart) & ":" & Cells(row, colEnd))) 'bombs here
varSum = Format(varSum, "hh:mm:ss")
Worksheets("Monthly_Summary").Select
Cells(row, 2).Select
ActiveCell.value = varSum
End Sub

 

[Dave Peterson]

Try:

varSum = Application.WorksheetFunction _
.Sum(Range(Cells(row,colStart),Cells(row, colEnd)))

 

[JLGWhiz]

varSum = Application.WorksheetFunction.Sum(Range(Cells(row,
colStart) & ":" & Cells(row, colEnd))) 'bombs here

Syntax should be:

varSum = Application.WorksheetFunction.Sum(Range(Cells(row,
colStart), Cells(row, colEnd)))

Without the Cells property being specifically qualified, the method will
apply only to the active sheet, which appears to be: Sheets(mon & "_" & Day
& "_" & yr).Select

If it were my code, I would put the sheet in a variable like:

Dim sh As Worksheet
Set sh = Sheets(mon & "_" & Day & "_" & yr)
varSum = Application.WorksheetFunction. _
Sum(Range(sh.Cells(row, colStart), sh.Cells(row, colEnd)))