Dim oCell As Excel.Range
Dim oSheet As Excel.Worksheet
Set oSheet = ActiveSheet


With Worksheets("MySheet")
For Each oCell in
oSheet.Range("F1:F30").SpecialCells(xlCellTypeCons tants)

Debug.Print Target.row
Next
End With

Stepping through this repeatedly prints 8. (Sometimes 11)

What makes it an 8, and why does it not move to the next row?

Thanks - Kirk

Is it because you use

Debug.Print Target.Row

whereas your range object is called oCell, so you should use

Debug.Print oCell.Row


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kirkm" wrote in message
...
Dim oCell As Excel.Range
Dim oSheet As Excel.Worksheet
Set oSheet = ActiveSheet


With Worksheets("MySheet")
For Each oCell in
oSheet.Range("F1:F30").SpecialCells(xlCellTypeCons tants)

Debug.Print Target.row
Next
End With

Stepping through this repeatedly prints 8. (Sometimes 11)

What makes it an 8, and why does it not move to the next row?

Thanks - Kirk

On Sat, 16 Sep 2006 07:58:21 +0100, "Bob Phillips"
wrote:

Is it because you use

Debug.Print Target.Row

whereas your range object is called oCell, so you should use

Debug.Print oCell.Row

Hi Bob,

Yes, hmmmm, but it just prints 1,2,3,4 instead of the
required rows. I'm not telling it what to look for - and can't see
where to.

Thanks - Kirk

Yes, you are indicating Constants ;
For Each oCell in oSheet.Range("F1:F30").SpecialCells(xlCellTypeCons tants)

So it depends what you consider "the required rows" .

NickHK

"kirkm" wrote in message
...
On Sat, 16 Sep 2006 07:58:21 +0100, "Bob Phillips"
wrote:

Is it because you use

Debug.Print Target.Row

whereas your range object is called oCell, so you should use

Debug.Print oCell.Row

Hi Bob,

Yes, hmmmm, but it just prints 1,2,3,4 instead of the
required rows. I'm not telling it what to look for - and can't see
where to.

Thanks - Kirk