Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
"Toppers" wrote in message
... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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 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. <<snipped -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
"Dave Peterson" wrote in message
... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Value of next visible mycell within a rng
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 Rasmus wrote: "Dave Peterson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ListBox1. add item myCell.entirerow | Excel Discussion (Misc queries) | |||
Custom Data Validation : Lock if MyCell = ""? | Excel Worksheet Functions | |||
Autoshapes not visible on spreadsheet but visible in print preview | Excel Discussion (Misc queries) | |||
Toggle text to be visible and not visible | Excel Programming | |||
Toggle Text in a column to be visible or not visible | Excel Programming |