ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VBA WorksheetFunction problem (https://www.excelbanter.com/excel-programming/318755-vba-worksheetfunction-problem.html)

Jeff Wright[_2_]

VBA WorksheetFunction problem
 
Greetings!

Can anyone tell me why I get a runtime error (1004) when running the simple
routine below? The data from B1 to B4 is 1, 2, 3, 4. The remainder of the
column is empty.

Sub cmdCountRows_Click()
Dim Leap As Integer
Leap = Application.WorksheetFunction.CountA("B1:B4")
MsgBox Leap
End Sub

Thanks for your help!

Jeff



David

VBA WorksheetFunction problem
 
Hi Jeff,
I don't get an error, but I do not get the correct answer either. I am using
V2003. I will kep an eye on this question, since mine is not working
correctly either.


"Jeff Wright" wrote:

Greetings!

Can anyone tell me why I get a runtime error (1004) when running the simple
routine below? The data from B1 to B4 is 1, 2, 3, 4. The remainder of the
column is empty.

Sub cmdCountRows_Click()
Dim Leap As Integer
Leap = Application.WorksheetFunction.CountA("B1:B4")
MsgBox Leap
End Sub

Thanks for your help!

Jeff




Jim Cone

VBA WorksheetFunction problem
 
Jeff,

When using a worksheet function you still have to use
VBA syntax to tell Excel what to evaluate.
Adding "Range" will do it...

Leap = Application.WorksheetFunction.CountA(Range("B1:B4" ))

Also, for what it's worth, the Worksheet function returns a double.

Regards,
Jim Cone
San Francisco, CA

----- Original Message -----
From: "Jeff Wright"
Newsgroups: microsoft.public.excel.programming
Sent: Friday, December 10, 2004 8:20 PM
Subject: VBA WorksheetFunction problem
Greetings!
Can anyone tell me why I get a runtime error (1004) when running the simple
routine below? The data from B1 to B4 is 1, 2, 3, 4. The remainder of the
column is empty.
Sub cmdCountRows_Click()
Dim Leap As Integer
Leap = Application.WorksheetFunction.CountA("B1:B4")
MsgBox Leap
End Sub
Thanks for your help!
Jeff



gocush[_28_]

VBA WorksheetFunction problem
 
Jeff
you need to add the word : range

Application.WorksheetFunction.CountA(range("B1:B4" ))

"Jeff Wright" wrote:

Greetings!

Can anyone tell me why I get a runtime error (1004) when running the simple
routine below? The data from B1 to B4 is 1, 2, 3, 4. The remainder of the
column is empty.

Sub cmdCountRows_Click()
Dim Leap As Integer
Leap = Application.WorksheetFunction.CountA("B1:B4")
MsgBox Leap
End Sub

Thanks for your help!

Jeff





All times are GMT +1. The time now is 10:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com