scroll rows up or down
 

I am using this code to find the current cell that matches the current date.
All works fine except I want to also have the current cell go to the top of
the window under the frozen headings. How can I alter the code to do this.

Dim SubStr
Dim MyCell As Range
Dim NextRow As Range
Sheets("Food").Select
Cells.Select
Selection.Columns.AutoFit
Range("A2:E2").Select
SubStr = Date
Call NxtRow(SubStr, MyCell, NextRow)
NextRow.Select
End Sub
Sub NxtRow(SubStr, MyCell, NextRow)
'
Cells.Select
Set MyCell = ActiveSheet.Range("A4")
Do
If MyCell.Value = "" Then
Exit Do
ElseIf MyCell.Value = SubStr Then
Set NextRow = MyCell
Exit Do
Else
Set MyCell = MyCell.Offset(1, 0)
End If
Loop
End Sub
--
Rick Rack

scroll rows up or down
 

Rick:

Call NxtRow(SubStr, MyCell, NextRow)
Application.Goto Reference:=NextRow, scroll:=True

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rick" wrote:

I am using this code to find the current cell that matches the current date.
All works fine except I want to also have the current cell go to the top of
the window under the frozen headings. How can I alter the code to do this.

Dim SubStr
Dim MyCell As Range
Dim NextRow As Range
Sheets("Food").Select
Cells.Select
Selection.Columns.AutoFit
Range("A2:E2").Select
SubStr = Date
Call NxtRow(SubStr, MyCell, NextRow)
NextRow.Select
End Sub
Sub NxtRow(SubStr, MyCell, NextRow)
'
Cells.Select
Set MyCell = ActiveSheet.Range("A4")
Do
If MyCell.Value = "" Then
Exit Do
ElseIf MyCell.Value = SubStr Then
Set NextRow = MyCell
Exit Do
Else
Set MyCell = MyCell.Offset(1, 0)
End If
Loop
End Sub
--
Rick Rack

scroll rows up or down
 

Thanks it worked perfect.
--
Rick Rack


"chijanzen" wrote:

Rick:

Call NxtRow(SubStr, MyCell, NextRow)
Application.Goto Reference:=NextRow, scroll:=True

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Rick" wrote:

I am using this code to find the current cell that matches the current date.
All works fine except I want to also have the current cell go to the top of
the window under the frozen headings. How can I alter the code to do this.

Dim SubStr
Dim MyCell As Range
Dim NextRow As Range
Sheets("Food").Select
Cells.Select
Selection.Columns.AutoFit
Range("A2:E2").Select
SubStr = Date
Call NxtRow(SubStr, MyCell, NextRow)
NextRow.Select
End Sub
Sub NxtRow(SubStr, MyCell, NextRow)
'
Cells.Select
Set MyCell = ActiveSheet.Range("A4")
Do
If MyCell.Value = "" Then
Exit Do
ElseIf MyCell.Value = SubStr Then
Set NextRow = MyCell
Exit Do
Else
Set MyCell = MyCell.Offset(1, 0)
End If
Loop
End Sub
--
Rick Rack