Value of next visible mycell within a rng

[Rasmus]

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

 

[Toppers]

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


For i = 1 To Rng.Count - 1
If Rng(i).value = Rng(i + 1).value Then
SalesForItem = SalesForItem + 1
Else
SalesForItem = 0
End If







HTH

 

[Rasmus]

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


For i = 1 To Rng.Count - 1
If Rng(i).value = Rng(i + 1).value Then
SalesForItem = SalesForItem + 1
Else
SalesForItem = 0
End If

Thanks, but this does still not work, as it does not seem to care about the
autofilter I have applied. The above routine starts off on the first visible
row correctly, but then proceeds to process every single line in the sheet -
hidden or not.

Please help Much appreciated.

Rasmus

 

[Dave Peterson]

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

 

[Toppers]

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]

Oh, oh.

I meant this:
For CellCtr = 1 To .Areas(AreaCtr).Cells.Count
just to loop through all the cells in that area.
(Some how I messed it up before I pasted into the post.)

Just for completeness:

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 = 1 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

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.

<<snipped>>

 

[Dave Peterson]

Ps. Thanks for the correction!

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.

 

[Rasmus]

Ps. Thanks for the correction!
Dave Peterson

Thanks for the routine - It did the job.

However, I was looking for a build-in function in Excel that could simply
tell me what the next cell within the defined range would be (as Excel MUST
know). I realise that your fine routine does this, but it eats up a lot of
CPU time if you use through a 40.000+ rows sheet.

But it did the job, so thanks again. I learned something as well.

Rasmus

 

[Dave Peterson]

Maybe you could pick up the values in the visible cells and plop them into an
array and then use that array:

Option Explicit
Sub testme()
Dim myRng As Range
Dim iCtr As Long
Dim myVal() As Variant
Dim myCell As Range

Set myRng = ActiveSheet.Range("a1:a20") _
.Cells.SpecialCells(xlCellTypeVisible)

ReDim myVal(1 To myRng.Cells.Count)
iCtr = 1
For Each myCell In myRng.Cells
myVal(iCtr) = myCell.Value
iCtr = iCtr + 1
Next myCell

End Sub