Scrolling Macro

[Hart]

I have created a macro which scrolls through a worksheet a fixed number of rows and then returns to the top and scrolls again. I need the "fixed" (14 in the sample below) number of rows to be variable based on the row number of a cell which contains specific text : "Today is"
Here is the macro..
Sub Scroll_Start()
NextTime = Now + TimeValue("00:00:02") 'Two second interval
ActiveWindow.Zoom = 125
Application.DisplayFullScreen = True
If ActiveWindow.VisibleRange.Rows(1).Row < 14 Then
ActiveWindow.SmallScroll Down:=1
Else
ActiveWindow.ScrollRow = 1
End If
Application.OnTime NextTime, "Scroll_Start"
End Sub

 

[Ray Bailey]

Hi, Assuming the "Today is" in column A you could try the following.


Sub CountRows()


Dim iRows As Integer

Range("A1").Select
Columns("A:A").Find(What:="Today is", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iRows = ActiveCell.Row


Scroll_Start (iRows)

End Sub


Then change your Sub statement to
Sub Scroll_Start(iRows As Integer)


And finally change the 14 with iRows


Hope this helps

 

[Hart]

Hi, Assuming the "Today is" in column A you could try the following.


Sub CountRows()


Dim iRows As Integer

Range("A1").Select
Columns("A:A").Find(What:="Today is", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iRows = ActiveCell.Row


Scroll_Start (iRows)

End Sub


Then change your Sub statement to
Sub Scroll_Start(iRows As Integer)


And finally change the 14 with iRows


Hope this helps.

Hi Ray,
Thanks for your reply. This appears to be close however I am getting an error message - Argument not optional. It may be that I haven't properly completed your instructions so have copied and pasted what I have below..

Sub CountRows()


Dim iRows As Integer

Range("A1").Select
Columns("A:A").Find(What:="Today is", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iRows = ActiveCell.Row - 6


Scroll_Start (iRows)

End Sub

Sub Scroll_Start(iRows)
NextTime = Now + TimeValue("00:00:02") 'Two second interval
If ActiveWindow.VisibleRange.Rows(1).Row < iRows Then
ActiveWindow.SmallScroll Down:=1
Else
ActiveWindow.ScrollRow = 1
End If
Application.OnTime NextTime, "Scroll_Start"
End Sub

 

[Ray Bailey]

Hi, Assuming the "Today is" in column A you could try the following.


Sub CountRows()


Dim iRows As Integer

Range("A1").Select
Columns("A:A").Find(What:="Today is", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iRows = ActiveCell.Row


Scroll_Start (iRows)

End Sub


Then change your Sub statement to
Sub Scroll_Start(iRows As Integer)


And finally change the 14 with iRows


Hope this helps.

Hi Ray,
Thanks for your reply. This appears to be close however I am getting an error message - Argument not optional. It may be that I haven't properly completed your instructions so have copied and pasted what I have below..

Sub CountRows()


Dim iRows As Integer

Range("A1").Select
Columns("A:A").Find(What:="Today is", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iRows = ActiveCell.Row - 6


Scroll_Start (iRows)

End Sub

Sub Scroll_Start(iRows)
NextTime = Now + TimeValue("00:00:02") 'Two second interval
If ActiveWindow.VisibleRange.Rows(1).Row < iRows Then
ActiveWindow.SmallScroll Down:=1
Else
ActiveWindow.ScrollRow = 1
End If
Application.OnTime NextTime, "Scroll_Start"
End Sub

Hi,

Looks like you've missed the "As Integer" option from the Sub statement

Sub Scroll_Start(iRows As Integer)


Ray

 

[Hart]

Hi,

Looks like you've missed the "As Integer" option from the Sub statement

Sub Scroll_Start(iRows As Integer)


Ray


Hi Ray;
I am still getting that message "Argument is not optional". Thanks for your help!

Sub CountRows()


Dim iRows As Integer

Range("A1").Select
Columns("A:A").Find(What:="Today is", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
iRows = ActiveCell.Row - 6


Scroll_Start (iRows)

End Sub

Sub Scroll_Start(iRows As Integer)

NextTime = Now + TimeValue("00:00:02") 'Two second interval
ActiveWindow.Zoom = 125
Application.DisplayFullScreen = True
If ActiveWindow.VisibleRange.Rows(1).Row < iRows Then
ActiveWindow.SmallScroll Down:=1
Else
ActiveWindow.ScrollRow = 1
End If
Application.OnTime NextTime, "Scroll_Start"
End Sub