Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 47
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,560
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,290
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 42
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Application.WorksheetFunction.Match problem Carl Brehm Excel Worksheet Functions 1 January 9th 05 02:08 PM
Evaluate or WorksheetFunction Problem SowBelly Excel Programming 2 August 28th 04 12:10 AM
Problem using "Find" WorksheetFunction in Excel VBA wadeni Excel Programming 1 August 12th 04 02:49 PM
Max WorksheetFunction solo Excel Programming 4 February 23rd 04 10:17 PM
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? Etien[_2_] Excel Programming 3 January 13th 04 04:07 PM


All times are GMT +1. The time now is 03:08 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"