I am trying to sum the value in 4 numbers across the same row if the value of
another cell in that row is a specific string. I need to be able to loop
through the sheet because there are multiple rows that I will need to search
for my criteria. Using the below sub I get a typemismatch error - I assume
it is because some of my cell values may be blank - is there a way to
correct this

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").value < "Year To Date" Then
Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"),
Range("I"), Range("L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

I did not understand why you used
'Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"), _
Range("I"), Range("L"))
What does 'Range("R") represent?

Try

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
..Calculation = xlCalculationManual
..ScreenUpdating = False
End With

With ActiveSheet

..Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

..DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").Value < "Year To Date" Then
'Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"), _
Range("I"), Range("L"))
..Cells(lRow, "R") = WorksheetFunction.Sum(.Cells(lRow, "B"), _
..Cells(lRow, "D"), .Cells(lRow, "F"), .Cells(lRow, "L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
..ScreenUpdating = True
..Calculation = CalcMode
End With

End Sub


"jeremiah" wrote:

I am trying to sum the value in 4 numbers across the same row if the value of
another cell in that row is a specific string. I need to be able to loop
through the sheet because there are multiple rows that I will need to search
for my criteria. Using the below sub I get a typemismatch error - I assume
it is because some of my cell values may be blank - is there a way to
correct this

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").value < "Year To Date" Then
Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"),
Range("I"), Range("L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

If .Cells(lRow, "B").Value < "Year To Date" Then
Cells(lRow, "R").Value = WorksheetFunction.Sum(Cells(lRow, "D").Value, _
Cells(lRow, "F").Value, Cells(lRow, "I").Value, Cells(lRow, "L").Value)
End If
--
Jim Cone
Portland, Oregon USA


"jeremiah"
wrote in message
I am trying to sum the value in 4 numbers across the same row if the value of
another cell in that row is a specific string. I need to be able to loop
through the sheet because there are multiple rows that I will need to search
for my criteria. Using the below sub I get a typemismatch error - I assume
it is because some of my cell values may be blank - is there a way to
correct this
-snip-

I not a vb expert by any means so I pick up alot of stuff from this forum and
others. I had read somewhere that you could use Range with
worksheetfunctions so gave it a try. None of my other attempts had worked
either so after a day of experimentation I decided to ask for help.

I have copied your sub below and do get some totals in the correct column
but on rows that shouldn't have any and they don't appear to be correct
totals. Will have to review to see what I am missing. Thanks for your help.



"Sheeloo" wrote:

I did not understand why you used
'Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"), _
Range("I"), Range("L"))
What does 'Range("R") represent?

Try

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").Value < "Year To Date" Then
'Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"), _
Range("I"), Range("L"))
.Cells(lRow, "R") = WorksheetFunction.Sum(.Cells(lRow, "B"), _
.Cells(lRow, "D"), .Cells(lRow, "F"), .Cells(lRow, "L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub


"jeremiah" wrote:

I am trying to sum the value in 4 numbers across the same row if the value of
another cell in that row is a specific string. I need to be able to loop
through the sheet because there are multiple rows that I will need to search
for my criteria. Using the below sub I get a typemismatch error - I assume
it is because some of my cell values may be blank - is there a way to
correct this

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").value < "Year To Date" Then
Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"),
Range("I"), Range("L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

With this sub I get "Unable to get sum property of the worksheetfunction class.

"Jim Cone" wrote:


If .Cells(lRow, "B").Value < "Year To Date" Then
Cells(lRow, "R").Value = WorksheetFunction.Sum(Cells(lRow, "D").Value, _
Cells(lRow, "F").Value, Cells(lRow, "I").Value, Cells(lRow, "L").Value)
End If
--
Jim Cone
Portland, Oregon USA


"jeremiah"
wrote in message
I am trying to sum the value in 4 numbers across the same row if the value of
another cell in that row is a specific string. I need to be able to loop
through the sheet because there are multiple rows that I will need to search
for my criteria. Using the below sub I get a typemismatch error - I assume
it is because some of my cell values may be blank - is there a way to
correct this
-snip-

I figured it out...I had value < "Year to date" it should have been = "Year
to Date" so I was pulling the wrong rows. Thanks again.

"jeremiah" wrote:

I not a vb expert by any means so I pick up alot of stuff from this forum and
others. I had read somewhere that you could use Range with
worksheetfunctions so gave it a try. None of my other attempts had worked
either so after a day of experimentation I decided to ask for help.

I have copied your sub below and do get some totals in the correct column
but on rows that shouldn't have any and they don't appear to be correct
totals. Will have to review to see what I am missing. Thanks for your help.



"Sheeloo" wrote:

I did not understand why you used
'Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"), _
Range("I"), Range("L"))
What does 'Range("R") represent?

Try

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").Value < "Year To Date" Then
'Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"), _
Range("I"), Range("L"))
.Cells(lRow, "R") = WorksheetFunction.Sum(.Cells(lRow, "B"), _
.Cells(lRow, "D"), .Cells(lRow, "F"), .Cells(lRow, "L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub


"jeremiah" wrote:

I am trying to sum the value in 4 numbers across the same row if the value of
another cell in that row is a specific string. I need to be able to loop
through the sheet because there are multiple rows that I will need to search
for my criteria. Using the below sub I get a typemismatch error - I assume
it is because some of my cell values may be blank - is there a way to
correct this

Sub SumHours()
Dim Firstrow As Long
Dim LastRow As Long
Dim lRow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range
Dim rngColour As Range
Dim blnColour As Boolean
Dim Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = 2
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For lRow = LastRow To Firstrow Step -1

If .Cells(lRow, "B").value < "Year To Date" Then
Range("R") = WorksheetFunction.Sum(Range("D"), Range("F"),
Range("I"), Range("L"))
End If

Next lRow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub