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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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,772
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,275
Messages
5,836,337
Members
430,421
Latest member
Natas

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