How to loop through all ranges in a worksheet
 

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.

How to loop through all ranges in a worksheet
 

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.

How to loop through all ranges in a worksheet
 

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.
.

How to loop through all ranges in a worksheet
 

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.

How to loop through all ranges in a 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.


.

How to loop through all ranges in a 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.


.

How to loop through all ranges in a 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.


.



.