for each in a range

[Walter Briscoe]

I run Excel 2003 and VBA 6.5.

I have a very limited understanding of VBA's Range object.

I hit this on a sheet which has relevant formulae in column C.
I want to treat the first formula as special.
I have the following code extract:
....
Dim PeakOyster As Boolean
Dim Trips As Range
....
Set Trips = Columns(3).SpecialCells(xlCellTypeFormulas)
PeakOyster = (Trips.Item(0)(1, -1) < 930 And Left(Cells(2, 2), 1) <> "S")
For Each C In Trips
....

Trips.Item(0) is evaluated without error.
This surprises me as Item is not shown as a member of Trips in the VBA
Locals Window, nor is it a documented member of the Range object.

The first formula is in $C$9.
However the Immediates Window shows
?trips.Item(0).address
$C$8
?trips.Address
$C$9:$C$10,$C$15,$C$18,$C$24,$C$26,$C$33,$C$35,$C$41,$C$43,$C$49,$C$51,$C$57:$C$58
?trips(1,-1).address
$A$9

i.e. trips.Item(0).address is $C$8, where I expect $C$9.

I can correct my code to
PeakOyster = (Trips(1, -1) < 930 And Left(Cells(2, 2), 1) <> "S")

I don't understand the logic of what I see.
I would value an explanation and a reference to documentation.
I'll even buy a book, if necessary.

P.S. Since starting to write this, I have found
?trips.Item(1).address
$C$9
i.e. Item is equivalent to a base 1 array and
for each Q in Columns(3).SpecialCells(xlCellTypeFormulas):? Q.address:next
$C$9
$C$10
$C$15
....

My code now contains:
' Trips.Item is absent from the Locals Window and is 1-based
PeakOyster = Trips.Item(1)(1, -1) < 930 And Not Cells(2, 2) Like "S*"

 

[GS]

Just a hint on how Excel and VBA manage ranges: There is never a zero
cell! Item(0) is 1 less than Item(1) and so if $C$9 is the 1st cell
(ie: Trips.Cells(1).Address) then it makes sense that Item(0) (ie:
Trips.Cells(-1).Address) is evaluated as $C$8!

When you dump a range into a variant you result a 2D array that is 1
based...

Dim vData As Variant
vData = Range("$C$9:$C$58")

...which results to vData having 50 rows and 1 column, which is the same
as...

Dim vData(1 To 50, 1 To 1)

...because there is no Row0 or Col0. Had your range started at $C$1
instead of $C$9 then Item(0) would either evaluate to ref the last cell
in ColB --OR-- throw an exception.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion