countif to find average

anisurrahman

New Member
Joined
Jul 3, 2008
Messages
4
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:
  ' ------ 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 ----
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Does the following approach not work for you?
Avg = WorksheetFunction.Average(Range("H11:L11"))
 

anisurrahman

New Member
Joined
Jul 3, 2008
Messages
4
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

New Member
Joined
Jul 3, 2008
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top