paulcherianc
07-25-2017, 07:49 AM
Dear Experts!
I have a macro which will insert Sum to a specific range as per the following coding. I need the macro to do two more calculations under the Sum and with the same Range selected.
1. Countif (Number Of Operatives) : =COUNTIF("Same Range Selected for the Sum"),">0") :
2. Another result (Average Hours) under the Countif, TotalHours/Number Of Operatives
So the final result should be as follows:
10
10
10
-----
TotalHours: 30
Number Of Operatives: 3
Average Hours: 10
Here is the code i did for calculating Hours, which is working perfectly. But could not fix Number Of Operatives & Average Hours just below theTotalHours. Please help:crying:
Sub TotalHours()
Dim LastColumn As Long, LastRow As Long, lngColumn As Long
LastColumn = Cells.Find(What:="*", After:=Range("AQ4"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For lngColumn = 11 To LastColumn
LastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row
With Cells(LastRow + 1, lngColumn)
.FormulaR1C1 = "=SUM(R4C:R" & LastRow & "C)"
End With
Next lngColumn
End Sub
Thanks in Advance!!
I have a macro which will insert Sum to a specific range as per the following coding. I need the macro to do two more calculations under the Sum and with the same Range selected.
1. Countif (Number Of Operatives) : =COUNTIF("Same Range Selected for the Sum"),">0") :
2. Another result (Average Hours) under the Countif, TotalHours/Number Of Operatives
So the final result should be as follows:
10
10
10
-----
TotalHours: 30
Number Of Operatives: 3
Average Hours: 10
Here is the code i did for calculating Hours, which is working perfectly. But could not fix Number Of Operatives & Average Hours just below theTotalHours. Please help:crying:
Sub TotalHours()
Dim LastColumn As Long, LastRow As Long, lngColumn As Long
LastColumn = Cells.Find(What:="*", After:=Range("AQ4"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For lngColumn = 11 To LastColumn
LastRow = Cells(Rows.Count, lngColumn).End(xlUp).Row
With Cells(LastRow + 1, lngColumn)
.FormulaR1C1 = "=SUM(R4C:R" & LastRow & "C)"
End With
Next lngColumn
End Sub
Thanks in Advance!!