Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 2nd, 2006, 06:36 AM
Authorized User
 
Join Date: Jan 2006
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Countif in VBA

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?

 
Old February 2nd, 2006, 09:41 PM
Registered User
 
Join Date: Jan 2006
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old October 30th, 2011, 04:12 AM
Registered User
 
Join Date: Oct 2011
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default Another VBA countif problem

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?
 
Old November 3rd, 2011, 07:37 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
The Following User Says Thank You to Shasur For This Useful Post:
kang0024 (November 4th, 2011)
 
Old November 4th, 2011, 07:17 AM
Registered User
 
Join Date: Oct 2011
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi it works fine! I am so grateful to you! May I know why I must insert "As Variant" in front?
 
Old November 4th, 2011, 08:57 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

"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.
 
Old November 4th, 2011, 12:54 PM
Registered User
 
Join Date: Oct 2011
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi

What I meant was, why is it the case that, when I omitted "As Variant", the function couldn't work?
 
Old November 4th, 2011, 08:18 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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

Last edited by mtranchi; November 4th, 2011 at 08:20 PM..
 
Old November 4th, 2011, 09:21 PM
Registered User
 
Join Date: Oct 2011
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi

It would be the best answer I could expect for. Thanks so much!
 
Old November 5th, 2011, 11:15 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA help davidbrooks Visual Basic 2008 Essentials 2 March 26th, 2008 07:25 PM
COUNTIF Function can't reference other workbooks kuznickic Excel VBA 1 October 5th, 2007 04:35 AM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM
WorksheetFunction.COUNTIF fails on the second pass Flower Access VBA 3 April 25th, 2007 02:45 AM
Excel VBA to SQL & back to VBA edesousa Excel VBA 1 June 1st, 2004 02:39 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.