I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

If you want to pass the name of the range, one way:



Public Function RowsInNamedRange(ByVal sName As String) As Long
On Error Resume Next
RowsInNamedRange = _
ActiveWorkbook.Names(sName).RefersToRange.Rows.Cou nt
End Function



In article
,
John wrote:

I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

Since NamedRange is already a range, you don't need to wrap it up in a
Range object. E.g., use


RowsInNameRange=NameRandge.Rows.Count

If, however, you wanted to pass the name of a range as a string, use

Function RowsInNamedRange(NamedRange As String) As Long
RowsInNamedRange=Range(NamedRange).Rows.Count
End Function

Since the number of rows in a worksheet is greater than the number
that can be stored in an Integer type variable, you should use As Long
instead of As Integer.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 6 Jan 2009 12:52:15 -0800 (PST), John
wrote:

I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

On Jan 6, 12:52*pm, John wrote:
I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

* * RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??


Figured it out.

-------------------------------
Public Function RowsInNamedRange(NamedRange As String)

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

?rowsinnamerange worksheets("somesheetnamehere").range("rngnamedran ge1")


John wrote:

I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

--

Dave Peterson

Function RowsInNamedRange(NamedRange As Range) As Long

RowsInNamedRange = NamedRange.Rows.Count

End Function


--
__________________________________
HTH

Bob

"John" wrote in message
...
I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------
Function RowsInNamedRange(NamedRange As Range) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

Hello John,

You have declared the argument "NamedRange" as a range object. The code
works as worksheet function because you are passing the name of the
named range. Excel passes the contents of the cell to the function,
which in this case, is a Range object named "Test". If the contents are
not a Range object and only text, the function fails.

Example 1
------------------------------------------------
Defined named range is Test. Address is A2:A10
In cell A1 is the text "Test"
In B1 is the formula =RowsInNamedRange(A1)
Result in B1 is 9
------------------------------------------------

Example 2
------------------------------------------------
In cell A2 is the text "Test1"
This is not a defined range, only text
In B2 is the formula =RowsInNamedRange(A2)
Result in B2 is #NAME?
------------------------------------------------

In VBA the Range method expects an address in string format or the name
of a named range, and converts this into a Range object. To make the
function work in both environments, change it as shown below...

--------------------------------------------------
Function RowsInNamedRange(ByVal NamedRange As String) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
---------------------------------------------------
Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47231

change
Function RowsInNamedRange(NamedRange As Range) As Integer
to
Function RowsInNamedRange(NamedRange As String) As Long

be sure to pass the name in quotes unless the argument is passed as a cell
ref containing the string-name.

There's no advantage to using As Integer vs As Long and Integer would fail
if the value is +32k (eg a whole column or rows).

Regards,
Peter T

"John" wrote in message
...
I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------


RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

How strange, when I posted I didn't see any other replies, a few minutes
later after having done so suddenly there's a whole bunch of them !

Peter T

"Peter T" <peter_t@discussions wrote in message
...
change
Function RowsInNamedRange(NamedRange As Range) As Integer
to
Function RowsInNamedRange(NamedRange As String) As Long

be sure to pass the name in quotes unless the argument is passed as a cell
ref containing the string-name.

There's no advantage to using As Integer vs As Long and Integer would fail
if the value is +32k (eg a whole column or rows).

Regards,
Peter T

"John" wrote in message
...
I'm not a VBA expert, and this is a very simple function...

I'm trying to create a function that will count the number of rows in
a named range. The function will be used in a formula in the workbook.

This is what I have:
-------------------------------


RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
-------------------------------

When I test via immediate window using:
? RowsInNamedRange "rngNamedRange1"

Where "rngNamedRange1" is a named range. I get a type mismatch error.
I know this is a simple fix...but just haven't figured it out yet. Any
pointers??

Thank you all for the considerate responses. For others' reference, I
ended up using the code below to return the row count of a given named
range.

------------------------------------------------
Public Function RowsInNamedRange(NamedRange As String)

On Error Resume Next
RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
------------------------------------------------




On Jan 6, 2:11*pm, Leith Ross
wrote:
Hello John,

You have declared the argument "NamedRange" as a range object. The code
works as worksheet function because you are passing the name of the
named range. Excel passes the contents of the cell to the function,
which in this case, is a Range object named "Test". If the contents are
not a Range object and only text, the function fails.

Example 1
------------------------------------------------
Defined named range is Test. Address is A2:A10
In cell A1 is the text "Test"
In B1 is the formula =RowsInNamedRange(A1)
Result in B1 is 9
------------------------------------------------

Example 2
------------------------------------------------
In cell A2 is the text "Test1"
This is not a defined range, only text
In B2 is the formula =RowsInNamedRange(A2)
Result in B2 is #NAME?
------------------------------------------------

In VBA the Range method expects an address in string format or the name
of a named range, and converts this into a Range object. To make the
function work in both environments, change it as shown below...

--------------------------------------------------
Function RowsInNamedRange(ByVal NamedRange As String) As Integer

RowsInNamedRange = Range(NamedRange).Rows.Count

End Function
---------------------------------------------------
Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=47231