Solved: CountIF to find Average using VBA

[anisurrahman]

Hi I am learning VBA and I am trying to use countIF but it is not working, everytime I tried to run this code it shows 'APPLICATION-DEFINED OR OBJECT-DEFINED ERROR'

Any idea friends where I am doing wrong

PS - to be honest I post the same topic - Mr Excel Forum and answered twice incorrectly.


My Code is :


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

[mdmackillop]

Countif can only contain 2 arguments. You have 6

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

[anisurrahman]

Countif can only contain 2 arguments. You have 6

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




Hi Thanks for your reply.. I tried your code but still it is showing Run-time Error '1004'- Application-defined or Object Defined error.

Any idea.. where is the problem mate... and do you think It will look for a Range H:L where row no is myRow,

With Worksheets("MasterData")

countWeekPattern = Application.CountIf(Range(.Cells(myRow, "H"), .Cells(myRow, "L")), ">0")
End With

[Aussiebear]

What is MyRow declared as?

[anisurrahman]

hi Aussiebear, thank you very much for your kind reply. myRow declared as variant . I have used
myRow = Application.Match(Sheets("Temp").[A11], .[A:A], 0) to find row number


part of the code
Private Sub saveHolButton_Click()

Dim myRow ''''''''''''''''''''''''''''''''''''''' 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

[mdmackillop]

Can you post a sample workbook?

[anisurrahman]

Can you post a sample workbook?

Hi Thank you very much for your reply. I have uploaded the sample workbook. My code is in worksheet (Temp). Please double click on Save button (design mode) to view the code.

The code is below

Private Sub saveHolButton_Click()
Dim myRow
Dim remainingHours As Double, totalhoursWork As Double
Dim countWeekPattern 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)
' 5 columns 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)

countWeekPattern = Application.CountIf(Range(.Cells(myRow, "H"), .Cells(myRow, "L")), ">0")
' Excel formulae is =M11/COUNTIF((H11:L11),">0") which I want to convert into VB

.Cells(myRow, "W") = .Cells(myRow, "T") / countWeekPattern

End If
End With

End Sub

[Aussiebear]

Just having a guess here but.... the CountIf function as described in Patrick Blattner's book "Using Microsoft Office Excel 2003" is CountIf(Range,Criteria).

So I'm wondering if the word "Range" in your formula is actually required?

Would this work?

countWeekPattern = Application.CountIf(.Cells(myRow, "H"), .Cells(myRow, "L")), ">0")
' Excel formulae is =M11/COUNTIF((H11:L11),">0")

[anisurrahman]

Just having a guess here but.... the CountIf function as described in Patrick Blattner's book "Using Microsoft Office Excel 2003" is CountIf(Range,Criteria).

So I'm wondering if the word "Range" in your formula is actually required?

Would this work?

countWeekPattern = Application.CountIf(.Cells(myRow, "H"), .Cells(myRow, "L")), ">0")
' Excel formulae is =M11/COUNTIF((H11:L11),">0")

hi Thanks again,, I afraid it doesn't work.... Application-defined or object defined error (Runtime error 1004)
countWeekPattern = Application.CountIf(.Cells(myRow, 8), .Cells(myRow, 12), ">0")

[Bob Phillips]

Just having a guess here but.... the CountIf function as described in Patrick Blattner's book "Using Microsoft Office Excel 2003" is CountIf(Range,Criteria).

So I'm wondering if the word "Range" in your formula is actually required?

Would this work?

countWeekPattern = Application.CountIf(.Cells(myRow, "H"), .Cells(myRow, "L")), ">0")

It is needed, otherwise you are just referencing two independent cells, and COUNTIF won't process them.

[anisurrahman]

It is needed, otherwise you are just referencing two independent cells, and COUNTIF won't process them.

hi xld any idea where is the problem.... thanks even with or without Range it is showing Application-defined ... error

[mdmackillop]

countWeekPattern = Application.CountIf(.Range(.Cells(myRow, "H"), .Cells(myRow, "L")), ">0")

[anisurrahman]

HURRAY...................... HURRAY,....... THANK YOU VERY MUCH.... PROBLEM SOLVED....... THANK YOU