Prev: How to access macros via tool buttons?
Next: Linking data with variable cell location
From: EAB1977 on 27 May 2010 11:27
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
From: Dave Peterson on 27 May 2010 12:00
Try:

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



EAB1977 wrote:
>
> 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
From: JLGWhiz on 27 May 2010 15:20
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)))



"EAB1977" <koolaid4u(a)yahoo.com> wrote in message
news:c7755346-b79c-45e3-b4cd-55e4de1e604e(a)i31g2000vbt.googlegroups.com...
> 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


 | 
Pages: 1
Prev: How to access macros via tool buttons?
Next: Linking data with variable cell location