Down one cell at a time
 

Hi im having problems programming a small function that
will go down one cell at a time comparing the cell to
previous one. My problems stems from trying to get the
range to move one cell down. I tried using
SendKeys "{DOWN}" but it cuases the application to just
hang. Changign the range select by interval of 1 won't cut
it becuase I use this in spreedsheets where I often filter
many rows so row 1 can be next to row 2000, looking for
row 2 column A won't help me. Does anyone know why
SendKeys "{DOWN}" won't work? Or is there a better way to
get the program to move down one cell at a time? Note I
tried usign Enter key instead of Down Arrow aswell, that
didnt help either.

Down one cell at a time
 

WCyrek,

Activecell.Offset(1,0).Select

John

"WCyrek" wrote in message
...
Hi im having problems programming a small function that
will go down one cell at a time comparing the cell to
previous one. My problems stems from trying to get the
range to move one cell down. I tried using
SendKeys "{DOWN}" but it cuases the application to just
hang. Changign the range select by interval of 1 won't cut
it becuase I use this in spreedsheets where I often filter
many rows so row 1 can be next to row 2000, looking for
row 2 column A won't help me. Does anyone know why
SendKeys "{DOWN}" won't work? Or is there a better way to
get the program to move down one cell at a time? Note I
tried usign Enter key instead of Down Arrow aswell, that
didnt help either.

Down one cell at a time
 

Tom Ogilvy gave me this code to increment down one cell at a time in a
single column on a filtered sheet. I have this as a separate macro and
insert a call to it when I need to go down a cell. It works great for me.

Ed

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

"WCyrek" wrote in message
...
Hi im having problems programming a small function that
will go down one cell at a time comparing the cell to
previous one. My problems stems from trying to get the
range to move one cell down. I tried using
SendKeys "{DOWN}" but it cuases the application to just
hang. Changign the range select by interval of 1 won't cut
it becuase I use this in spreedsheets where I often filter
many rows so row 1 can be next to row 2000, looking for
row 2 column A won't help me. Does anyone know why
SendKeys "{DOWN}" won't work? Or is there a better way to
get the program to move down one cell at a time? Note I
tried usign Enter key instead of Down Arrow aswell, that
didnt help either.

Down one cell at a time
 

Thank you both! I will be sure to try both of your
solutions ASAP!

-----Original Message-----
Tom Ogilvy gave me this code to increment down one cell
at a time in a
single column on a filtered sheet. I have this as a
separate macro and
insert a call to it when I need to go down a cell. It
works great for me.

Ed

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

"WCyrek" wrote in
message
...
Hi im having problems programming a small function that
will go down one cell at a time comparing the cell to
previous one. My problems stems from trying to get the
range to move one cell down. I tried using
SendKeys "{DOWN}" but it cuases the application to just
hang. Changign the range select by interval of 1 won't
cut
it becuase I use this in spreedsheets where I often
filter
many rows so row 1 can be next to row 2000, looking for
row 2 column A won't help me. Does anyone know why
SendKeys "{DOWN}" won't work? Or is there a better way
to
get the program to move down one cell at a time? Note I
tried usign Enter key instead of Down Arrow aswell, that
didnt help either.


.

Down one cell at a time
 

Ed,

I tried your way, seems closer to perfect. However I find
it that once it gets to the end of the filtered rows where
I have Loop Until (ActiveCell.FormulaR1C1 = "") the
ptogram just hangs and I must use ctrl break to exit. And
the debuger always points to End If in Incriment1 or On
Error GoTo 0.

Here is what im working with for reference

Public MyStr As String
Public CounterN As Integer

Private Sub CaseCommandButton_Click()
Counter.Hide
Range("A1").Select
Call Increment1
MyStr = "Start"
CounterN = 0
Do
Call CountUP
Loop Until (ActiveCell.FormulaR1C1 = "")
MsgBox ("The final number is " & CounterN)
Unload Counter
End Sub

Private Function CountUP()
If ActiveCell.FormulaR1C1 = MyStr Then
MyStr = ActiveCell.FormulaR1C1
Else
MyStr = ActiveCell.FormulaR1C1
CounterN = CounterN + 1
End If
Call Increment1
End Function


-----Original Message-----
Tom Ogilvy gave me this code to increment down one cell
at a time in a
single column on a filtered sheet. I have this as a
separate macro and
insert a call to it when I need to go down a cell. It
works great for me.

Ed

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

"WCyrek" wrote in
message
...
Hi im having problems programming a small function that
will go down one cell at a time comparing the cell to
previous one. My problems stems from trying to get the
range to move one cell down. I tried using
SendKeys "{DOWN}" but it cuases the application to just
hang. Changign the range select by interval of 1 won't
cut
it becuase I use this in spreedsheets where I often
filter
many rows so row 1 can be next to row 2000, looking for
row 2 column A won't help me. Does anyone know why
SendKeys "{DOWN}" won't work? Or is there a better way
to
get the program to move down one cell at a time? Note I
tried usign Enter key instead of Down Arrow aswell, that
didnt help either.


.

Down one cell at a time
 

Private Sub CaseCommandButton_Click()
Counter.Hide
MyStr = "Start"
CounterN = Application.countif(Range("A:A"),MyStr)
MsgBox ("The final number is " & CounterN)
Unload Counter
End Sub

--
Regards,
Tom Ogilvy


wrote in message
...
Ed,

I tried your way, seems closer to perfect. However I find
it that once it gets to the end of the filtered rows where
I have Loop Until (ActiveCell.FormulaR1C1 = "") the
ptogram just hangs and I must use ctrl break to exit. And
the debuger always points to End If in Incriment1 or On
Error GoTo 0.

Here is what im working with for reference

Public MyStr As String
Public CounterN As Integer

Private Sub CaseCommandButton_Click()
Counter.Hide
Range("A1").Select
Call Increment1
MyStr = "Start"
CounterN = 0
Do
Call CountUP
Loop Until (ActiveCell.FormulaR1C1 = "")
MsgBox ("The final number is " & CounterN)
Unload Counter
End Sub

Private Function CountUP()
If ActiveCell.FormulaR1C1 = MyStr Then
MyStr = ActiveCell.FormulaR1C1
Else
MyStr = ActiveCell.FormulaR1C1
CounterN = CounterN + 1
End If
Call Increment1
End Function


-----Original Message-----
Tom Ogilvy gave me this code to increment down one cell
at a time in a
single column on a filtered sheet. I have this as a
separate macro and
insert a call to it when I need to go down a cell. It
works great for me.

Ed

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

"WCyrek" wrote in
message
...
Hi im having problems programming a small function that
will go down one cell at a time comparing the cell to
previous one. My problems stems from trying to get the
range to move one cell down. I tried using
SendKeys "{DOWN}" but it cuases the application to just
hang. Changign the range select by interval of 1 won't
cut
it becuase I use this in spreedsheets where I often
filter
many rows so row 1 can be next to row 2000, looking for
row 2 column A won't help me. Does anyone know why
SendKeys "{DOWN}" won't work? Or is there a better way
to
get the program to move down one cell at a time? Note I
tried usign Enter key instead of Down Arrow aswell, that
didnt help either.


.