countif to find average

[anisurrahman]

Hi

This is my first post.

I have five columns Column (H,I,J,K,L), I am trying to check these columns cell value and then find average. I can use excel formulae [=M11/COUNTIF((H11:L11),">0")], but I need to convert this formulae into VBA.

  ' ------ code doesn't work ----------   
                countWeekPattern = Application.CountIf(Sheets("MasterData").Range(Cells(myRow, "H"), _
                Cells(myRow, "I")), Cells(myRow, "J"), Cells(myRow, "K"), _
                Cells(myRow, "L"), ">0")
 
 'APPLICATION-DEFINED OR OBJECT-DEFINED ERROR

My full code ----

Private Sub saveHolButton_Click()
Dim myRow
'to find row
Dim sfind  As String
Dim cl     As Range
Dim remainingHours As Double, totalhoursWork As Double
 
 
        With Sheets("MasterData")
 
        myRow = Application.Match(Sheets("Temp").[A11], .[A:A], 0)
        'find the userID Row
 
            If IsNumeric(myRow) Then
            'if found userID
 
                .Cells(myRow, "Q") = Sheets("Temp").[c17]
                'assign the contents of C17 of sheet Temp to corresponding cell in MasterData sheet
 
                .Cells(myRow, "S") = .Cells(myRow, "Q") + .Cells(myRow, "R")
                ' sum of total booked holiday January to December
 
                .Cells(myRow, "T") = .Cells(myRow, "P") - .Cells(myRow, "S")
                 remainingHours = .Cells(myRow, "T")
 
                  totalhoursWork = .Cells(myRow, "M")
                  ' sum of column H,I,J,K and L
                  ' How do I find average of Sum(H:L) 
                  ' To find average 5 columns cell value - so it should be sum(H:L)/5
                  'If one of the column cell value is 0 then it should sum(H:L)/4
                  'if two of the columns cell value is 0 then it should sum(H:L)/3
                  ' I can use the formulae in Excel but how do i convert it into VBA
                  ' Excel formula =M11/COUNTIF((H11:L11),">0")
                  ' M11 is total of (H,I,J,K and L)
 
           ' ------ code doesn't work ----------   
                countWeekPattern = Application.CountIf(Sheets("MasterData").Range(Cells(myRow, "H"), _
                Cells(myRow, "I")), Cells(myRow, "J"), Cells(myRow, "K"), _
                Cells(myRow, "L"), ">0")
 
 'APPLICATION-DEFINED OR OBJECT-DEFINED ERROR
 
 
 
                worksheets("MasterData").Cells(myRow, "W") = .Cells(myRow, "T") / countWeekPattern
 
 
 
 
 
            End If
 
                End With
        end sub

 

[njimack]

Does the following approach not work for you?
Avg = WorksheetFunction.Average(Range("H11:L11"))

 

[anisurrahman]

hi thanks for your quick reply. problem is I want to check the cells value first, if one of the cell value is 0 then it should divide the total of 5 columns by 4, as I explained in my post. Because I am counting holiday entitlements, so it is important to know whether the cell value is 0 or not. And my row for example H11 is not fixed it is actually cells(myrow,8), so I wanted to use countif

countWeekPattern = Application.CountIf(Sheets("MasterData").Range(Cells(myRow, "H"), _
Cells(myRow, "I")), Cells(myRow, "J"), Cells(myRow, "K"), _
Cells(myRow, "L"), ">0")


Any idea ..

 

[njimack]

It might be failing because you're not qualifying the sheet with each use of a range. Maybe something like this?

With Sheets("MasterData")
countWeekPattern = Application.CountIf(Range(.Cells(myRow, "H"), _
.Cells(myRow, "I")), .Cells(myRow, "J"), .Cells(myRow, "K"), _
.Cells(myRow, "L"), ">0")
End With

 

[anisurrahman]

It might be failing because you're not qualifying the sheet with each use of a range. Maybe something like this?

With Sheets("MasterData")
countWeekPattern = Application.CountIf(Range(.Cells(myRow, "H"), _
.Cells(myRow, "I")), .Cells(myRow, "J"), .Cells(myRow, "K"), _
.Cells(myRow, "L"), ">0")
End With

Hi Thanks for your reply.

I tried and still it is showing Runtime Error '1004', Application-Defined or object-defined error.

Sorry any Idea

 

[anisurrahman]

any help from anyone