Ps. Thanks for the correction!

Toppers wrote:

Dave,
I tried your code as a learning exercise ... and now understand
more about areas! .. but it appeared to me that it compared the last entry
in one area with the first in the next. It does not compare values within an
area as I expected i.e we are trying to compare consective values in
non-contiguous ranges.

Have I misunderstood what you are doing as the loop below only executes once
for each area.

Excuse my ignorance but I am trying to understand how this functionality
works.

For CellCtr = .Areas(AreaCtr).Cells.Count _
To .Areas(AreaCtr).Cells.Count

TIA.

"Dave Peterson" wrote:

This seemed to work ok for me:

Option Explicit
Sub testme()

Dim j As Long
Dim myRng As Range
Dim myCell As Range
Dim CellCtr As Long
Dim AreaCtr As Long
Dim NextCell As Range


'ActiveSheet.Rows(2).AutoFilter _
Field:=32, Criteria1:="" & CStr(SalesDate), Operator:=xlAnd

j = Cells(65536, 9).End(xlUp).Row
Set myRng = Range("I2:I" & j).SpecialCells(xlCellTypeVisible)

For AreaCtr = 1 To myRng.Areas.Count
With myRng
For CellCtr = .Areas(AreaCtr).Cells.Count _
To .Areas(AreaCtr).Cells.Count
Set myCell = .Areas(AreaCtr).Cells(CellCtr)
Set NextCell = Nothing
If CellCtr = .Areas(AreaCtr).Cells.Count Then
If AreaCtr < myRng.Areas.Count Then
Set NextCell = .Areas(AreaCtr + 1).Cells(1)
Else
'out of cells, what to do?
End If
Else
Set NextCell = .Areas(AreaCtr).Cells(CellCtr + 1)
End If
If NextCell Is Nothing Then
MsgBox "MyCell is: " & myCell.Address(0, 0) & vbLf _
& "No next cell!"
Else
MsgBox "MyCell is: " & myCell.Address(0, 0) & vbLf _
& "NextCell is: " & NextCell.Address(0, 0)
End If
Next CellCtr
End With
Next AreaCtr

End Sub

Rasmus wrote:

My code looks - so far - like this:
================================================== =========
ActiveSheet.Rows(2).AutoFilter Field:=32, Criteria1:="" & CStr(SalesDate),
Operator:=xlAnd

j = Cells(65536, 9).End(xlUp).Row
Set rng = Range("I2:I" & j).SpecialCells(xlCellTypeVisible)

For Each Mycell In rng.Cells
' PUT CODE HERE
Next Mycell
================================================== =========

Now, how do I get the value of the next MyCell in the defined rng as I want
to do some code that compares the active MyCell against the next MyCell in
line ? fx:

If Mycell = Mycell.next then ' does NOT work
SalesForItem = SalesForItem + 1
Else
SalesForItem = 0
End If

I've tried using the offset command; Mycell.Next.Offset(1, 0) , but that
does not work as I have an Autofilter active.

Please help.

Rasmus

--

Dave Peterson


--

Dave Peterson