Unable to get the countifs property of the worksheetfunction class

[Godders199]

Hi, can anyone tell me what I am doing wrong here, I keepgetting the following error “Unable to get the countifs property of theworksheetfunction class

Dim lastrowcolumnz As Long
lastrowcolumnz = Range("a" &Rows.Count).End(xlUp).Row
For i = 2 To lastrowcolumnz
Cells(i, 20) =Application.WorksheetFunction.CountIfs(Sheets("HomeInsurance").Range("B:B"), Sheets("Summary").Cells(i,1) > 0, Cells(i, 20).Value = "Home Insurance", "")
Next i
If I type the formula in the cell it returns the results,just can get the vba code to work.

Have looked at loads of examples, just cant get past thiserror



thanks

 

[Fluff]

That is not a valid formula.
What are you trying to do?

 

[Godders199]

Hi i am trying to get the following formula in column t to last row

=IF(COUNTIFS('HomeInsurance'!B:B,Summary!A2)>0,"Home Insurance","")

 

[Fluff]

Try

Dim lastrowcolumnz As Long
lastrowcolumnz = Range("a" & Rows.Count).End(xlUp).Row
With Range("T2:T" & lastrowcolumnz)
   .Formula = "=if(CountIfs('HomeInsurance'!B:B, Summary!A2)> 0,""Home Insurance"")"
   .Value = .Value
End With

 

[RoryA]

Just FYI, the equivalent VBA would have been:

If Application.WorksheetFunction.CountIf(Sheets("HomeInsurance").Range("B:B"), Sheets("Summary").Cells(i,1)) > 0 Then
Cells(i, 20).Value = "Home Insurance"
Else
Cells(i, 20) = ""
End If

 

[Godders199]

thanks both for your help,

 

[Fluff]

Your welcome & thanks for the feedback