Countif in VBA

xbenx · February 2nd, 2006, 06:36 AM

hi, i have this countif formula that i need it to run thru all the rows of data..

=COUNTIF(A1:A3772,"<"&A*) where * range from 1 to 1000

i wrote the following vba code but it give an 'application defined or object defined error'

Code:

Sub fill()
    Dim irow, lastrow As Integer
    Dim frmla As String

    lastrow = ActiveSheet.UsedRange.Rows.Count
    For irow = 1 To lastrow
      frmla = "=COUNTIF(A1:A" & lastrow & ",<&A" & irow & ")"
      ActiveSheet.Cells(irow, 4).Formula = frmla
    Next irow

End Sub

anyone knows where went wrong?

Alseikhan · February 2nd, 2006, 09:41 PM

The problem here lies with the way VBA interprets use of quotation marks. Normally, quotation marks define beginning and end of a string, and would not be included as part of the formula string

Thus your procedure generates the following formula string in the first cell:
=COUNTIF(A1:A3772,<&A1)

when in fact what is needed is this string:
=COUNTIF(A1:A3772,â<â&A1)

The first one would crash VBA because it is an invalid Excel formula string.

The issue then is to force VBA to treat quotation marks as part of the string, and not string separators. The solution is to tack on 2 additional quotation marks preceding each formula quotation component as follows:

frmla = "=COUNTIF(A1:A" & lastrow & "," & """<" & """&A" & irow & ")"

Here, the first cell formula will show:
=COUNTIF(A1:A3772,â<â&A1)

Best,

Alseikhan

kang0024 · October 30th, 2011, 04:12 AM

I wanna perform excel countif function for a variable criteria cell y, thus i wrote a VBA function as below:

Code:

Function prb(x As Range, y As Double)
prb(x, y) = Application.WorksheetFunction.CountIf(x, ">y")
End Function

However the function returns #VALUE! on excel sheet. Can anyone tell me what the problem is?

Shasur · November 3rd, 2011, 07:37 AM

Hi

Cacn you try something like

Code:

Function prb(x As Range, y As Double) As Variant
prb = Application.WorksheetFunction.CountIf(x, ">" & y)
End Function

Cheers
Shasur

kang0024 · November 4th, 2011, 07:17 AM

Hi it works fine! I am so grateful to you! May I know why I must insert "As Variant" in front?

mtranchi · November 4th, 2011, 08:57 AM

"As Variant" is the return type of the function. The way you have the function written, you don't return a value, so you could use a Sub instead of a Function.

kang0024 · November 4th, 2011, 12:54 PM

Hi

What I meant was, why is it the case that, when I omitted "As Variant", the function couldn't work?

mtranchi · November 4th, 2011, 08:18 PM

that question is above my pay-grade, you'd have to ask the MS Office team about that I'm guessing. If you don't give a function a return type, it defaults to type Variant, so it should make no difference. But I've personally found, and have read books who also say, that there are bugs in VBA, so you should keep that in mind when programming in VBA. Usually it's your fault if it doesn't work right, but not always.

also, you should always specify a return type, since the type Variant soaks up more memory, and also causes your program to run slower, as it needs to run a method to figure out what kind of variable is in fact contained within a Variant. In the case of function calls like yours, that will happen every time the function is called, and if you're running down a couple of thousand rows, it can be a pretty significant hit in terms of performance.

sorry i couldn't answer your question better than that,

mike

kang0024 · November 4th, 2011, 09:21 PM

Hi

It would be the best answer I could expect for. Thanks so much!

mtranchi · November 5th, 2011, 11:15 PM

you're quite welcome, i have learned much from these forums, therefore i try to give back. I'm here if you need me.

mike