Range Issue

[pratiksuhasaria]

this is the excel forumla which i used to calculate count of values which are less than and equal to E2
=COUNTIF(Sheet1!D2:D13,"<="&E2)

but i want this same thing to be used in vba so i used this function
Application.WorksheetFunction.CountIf(rng, Range("<=" & "E2"))

but it showing error of type mismatch

help me out with this

 

[Rick Rothstein]

this is the excel forumla which i used to calculate count of values which are less than and equal to E2
=COUNTIF(Sheet1!D2:D13,"<="&E2)

but i want this same thing to be used in vba so i used this function
Application.WorksheetFunction.CountIf(rng, Range("<=" & "E2"))

but it showing error of type mismatch

help me out with this

Untested (about to go to sleep), but I think it should be this way...

Application.WorksheetFunction.CountIf(rng, "<=" & Range("E2"))

Of course, you have to assign this to something in order to preserve the calculated value.

 

[Michael M]

Try

Sub MM1()
Dim rng As Range, val As Long
Set rng = Sheets("Sheet1").Range("D2:D13")
Val = Application.WorksheetFunction.CountIf(rng, "<=" & Range("E2"))
End Sub

 

[pratiksuhasaria]

Thanks For your help it worked..