I'm trying to programmatically determine if a range exists
in an existing workbook. I'm using the following code and
it's erroring out:

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

I'm getting "Compile Error: Argument not optional"
applying to ".Range.Count". Thing is, I'm trying to
use ".Range" as a collection because there's supposed to
be a range collection w/a count propery.

Can anyone help me here? Thanks.

What do you mean by ranges?
Do you use named ranges or do you want to loop through all cells
in the worksheet.

Or all cells in a range(a1:c20 or so)

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Nanette" wrote in message ...
I'm trying to programmatically determine if a range exists
in an existing workbook. I'm using the following code and
it's erroring out:

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

I'm getting "Compile Error: Argument not optional"
applying to ".Range.Count". Thing is, I'm trying to
use ".Range" as a collection because there's supposed to
be a range collection w/a count propery.

Can anyone help me here? Thanks.

Hi, You cannot use the range.count that way,
you can use it this way sheet1.range("a1:a33").count
Which will obviusly give you 33 as a value.

Are looking for something specifically in a range?
Earl

-----Original Message-----
I'm trying to programmatically determine if a range
exists
in an existing workbook. I'm using the following code
and
it's erroring out:

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

I'm getting "Compile Error: Argument not optional"
applying to ".Range.Count". Thing is, I'm trying to
use ".Range" as a collection because there's supposed to
be a range collection w/a count propery.

Can anyone help me here? Thanks.
.

I think you don't understand what Count does.

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

The error message you are getting is because the VBE wants
you to define a Range.

i.e.
--------
..Range("A1:A5").Count
--------

Would return 5

Count does not return a count of Range Objects that have
been defined for that worksheet.

You're right, that's not what I want it to do.

I have a named range and I want to test if it exists (i.e.
that nobody has deleted it accidentally.) So yes, I want
to be able to loop through all of the named ranges in
either the workbook or the worksheet. Is there a way to do
that? Thanks.

Nanette
-----Original Message-----
I think you don't understand what Count does.

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

The error message you are getting is because the VBE wants
you to define a Range.

i.e.
--------
..Range("A1:A5").Count
--------

Would return 5

Count does not return a count of Range Objects that have
been defined for that worksheet.


.

You can loop through all the names in a workbook with code like

Dim Nm As Name
For Each Nm In ThisWorkbook.Names
' do something with Nm
Next Nm

You can test whether a name exists with code like

Dim N As Integer
On Error Resume Next
N = Len(ThisWorkbook.Names("TheName").Name)
If N 0 Then
' name exists
Else
' name doesn't exist
End If
On Error Goto 0

Somethimes a name exists, but contains a #REF error because the
range to which it refered has been deleted. To test this
condition, use code like

Dim Nm As Name
Set Nm = ThisWorkbook.Names("TheName")
If InStr(Nm.RefersTo,"#REF") 0 Then
' name contains a #REF error
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
...
You're right, that's not what I want it to do.

I have a named range and I want to test if it exists (i.e.
that nobody has deleted it accidentally.) So yes, I want
to be able to loop through all of the named ranges in
either the workbook or the worksheet. Is there a way to do
that? Thanks.

Nanette
-----Original Message-----
I think you don't understand what Count does.

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

The error message you are getting is because the VBE wants
you to define a Range.

i.e.
--------
..Range("A1:A5").Count
--------

Would return 5

Count does not return a count of Range Objects that have
been defined for that worksheet.


.

Awesome! Major help! Thanks.

Nanette
-----Original Message-----
You can loop through all the names in a workbook with
code like

Dim Nm As Name
For Each Nm In ThisWorkbook.Names
' do something with Nm
Next Nm

You can test whether a name exists with code like

Dim N As Integer
On Error Resume Next
N = Len(ThisWorkbook.Names("TheName").Name)
If N 0 Then
' name exists
Else
' name doesn't exist
End If
On Error Goto 0

Somethimes a name exists, but contains a #REF error
because the
range to which it refered has been deleted. To test this
condition, use code like

Dim Nm As Name
Set Nm = ThisWorkbook.Names("TheName")
If InStr(Nm.RefersTo,"#REF") 0 Then
' name contains a #REF error
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


wrote in message
...
You're right, that's not what I want it to do.

I have a named range and I want to test if it exists
(i.e.
that nobody has deleted it accidentally.) So yes, I want
to be able to loop through all of the named ranges in
either the workbook or the worksheet. Is there a way to
do
that? Thanks.

Nanette
-----Original Message-----
I think you don't understand what Count does.

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff
Next i
End With
========

The error message you are getting is because the VBE
wants
you to define a Range.

i.e.
--------
..Range("A1:A5").Count
--------

Would return 5

Count does not return a count of Range Objects that
have
been defined for that worksheet.


.



.