PDA

View Full Version : [SOLVED] VBA Count IF help



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!!

mdmackillop
07-25-2017, 08:03 AM
Can you post a sample workbook.

paulcherianc
07-25-2017, 10:17 PM
Yes. Please find attached excel book where I have given the original and the result in two separate sheets with the macro to find Total Hours. Thanks for looking into this issue!

mdmackillop
07-26-2017, 12:46 AM
You have a circular reference in AO4

paulcherianc
07-26-2017, 02:00 AM
Yes i have fixed it thanks!

paulcherianc
07-26-2017, 02:01 AM
Dim LastColumn As Long, LastRow As Long, lngColumn As Long


LastColumn = Cells.Find(What:="*", After:=Range("AQ4"), _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

With Cells(LastRow + 1, "J")
.Value = "Total Hours"
.Offset(1).Value = "Total Operatives"
.Offset(2).Value = "Average Hours"
End With

With Range(Cells(LastRow + 1, "K"), Cells(LastRow + 1, "AO"))
.Formula = "=SUM(K4:K" & LastRow & ")"
.Offset(1).Formula = "=COUNTIF(K4:K" & LastRow & ","">0"")"
.Offset(2).Formula = "=K" & (LastRow + 1) & "/K" & (LastRow + 2)
End With

With Cells(LastRow + 1, "AP")
.Formula = "=SUM(AP4:AP" & LastRow & ")"
.Offset(1).Formula = "=SUM(K" & (LastRow + 2) & ":AO" & (LastRow + 2) & ")"
.Offset(2).Formula = "=AP" & (LastRow + 1) & "/AP" & (LastRow + 2)
End With

With Cells(LastRow + 1, "AQ")
.Formula = "=SUM(AQ4:AQ" & LastRow & ")"
End With

With Range(Cells(LastRow + 1, "J"), Cells(LastRow + 1, "AQ"))
.Borders.LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
'.Interior.Color = vbGreen
End With

With Range(Cells(LastRow + 2, "J"), Cells(LastRow + 2, "AP"))
.Borders.LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
'.Interior.Color = vbGreen
.Offset(1).Borders.LineStyle = xlContinuous
.Offset(1).Borders(xlInsideVertical).LineStyle = xlContinuous
'.Offset(1).Interior.Color = vbGreen
End With

Range(Cells(LastRow + 1, "J"), Cells(LastRow + 3, "AQ")).Font.Bold = True