Macro to scroll down one row at a time

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,360
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have an Excel file with paragraph after paragraph running down a column. These paragraphs will be read and I'd like a macro to scroll one row at a time based on the reading time of each row plus second as a buffer. Column I holds the time value for that paragraph on the same row. With the macro below, it works if I hard code the timevalue to wait before scrolling down, but instead of hard coding the timevalue, I want the timevalue to come from Column I.

With this I get a type mismatch error 13

VBA Code:
Sub CreateScrollDown2()


    Dim LastRow As Long, CurrRow As Long, r As Range, myTime As String
   
    LastRow = Sheet1.Range("ScriptureEnd")
    CurrRow = Sheet1.Range("ScriptureStart")
   
    With ActiveWindow
        Do
            myTime = TimeValue(Range("I" & CurrRow).Text)
            Application.Wait (Now + myTime)
            .SmallScroll down:=1
            CurrRow = CurrRow + 1
        Loop While CurrRow < LastRow
    End With
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
just try this... i am guessing here, but i have never converted my increment to timevalue for this sort of thing
VBA Code:
 myTime = Val(Range("I" & CurrRow).Text)
 
Upvote 0
This passes without throwing an error, but myTime ends up being "0"
 
Upvote 0
then there must be a content problem in the particular row of data. when it debugs, check the CurrRow and have a look at the cell contents.
 
Upvote 0
Okay, after doing some more research, I have found the code below to work. I changed CurrRow and LastRow to StartRow and EndRow respectively and have also hard coded those numbers for this testing.

Now, how can I consider the milliseconds instead of the time being just rounded to the whole seconds? I've read where VBA does not easily handle the milliseconds, but have also read there are ways. Just not understanding them. Is this something that should use the Sleep function instead?

Column C is formatted as ss.0

VBA Code:
Sub CreateScrollDown2()

    Dim newSecond   As Long
    Dim StartRow    As Long: StartRow = 3
    Dim EndRow      As Long: EndRow = 8
    Dim waitTime
   
    Application.Goto Sheet1.Range("A" & StartRow), scroll:=True
   
    With ActiveWindow
        Do
            newSecond = Second(Sheet1.Range("C" & StartRow))
            waitTime = TimeSerial(0, 0, newSecond)
            Application.Wait (Now + waitTime)
            .SmallScroll down:=1
            StartRow = StartRow + 1
        Loop While StartRow < EndRow
    End With
   
    Application.Goto Sheet2.Range("A1"), scroll:=True
   
End Sub

Book1
ABC
1Hdr1Hdr2Hdr3
21Video provides a powerful way to help you prove your point.03.8
32When you click Online Video, you can paste in the embed code for the video you want to add.06.5
43You can also type a keyword to search online for the video that best fits your document.05.8
54To make your document look professionally produced, Word provides header, footer, cover page, and text box designs that complement each other.07.2
65For example, you can add a matching cover page, header, and sidebar.04.1
76Click Insert and then choose the elements you want from the different galleries.04.5
87Themes and styles also help keep your document coordinated.03.1
98When you click Design and choose a new Theme, the pictures, charts, and SmartArt graphics change to match your new theme.07.2
109When you apply styles, your headings change to match the new theme.04.1
1110Save time in Word with new buttons that show up where you need them.04.8
Sheet1
Cell Formulas
RangeFormula
C2:C11C2=((LEN(B2)-LEN(SUBSTITUTE(B2," ",""))+1)/180)/1400
 
Upvote 0
Hi diddi and thanks for your help. For column C I just went with rounding the milliseconds up to the second. Works for me.

Instead of FLOOR, change it to CEILING.MATH

 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top