VBA loop question

[Pinaceous]

Hi All,

I'm using this sub below to loop a data list to create reports where it runs until the number specified inside of this code.

This number changes according to how much data there is on the list which changes every time.

   Sub Test()

Dim i As Integer

i = 2

Do Until i > 16

    'Change # According to How Many Lines Of Data You Have

    Call Macro1
  
    Call Save_ActSht_as_Pdf

    Application.Wait (Now + TimeValue("0:00:1"))

     i = i + 1

Loop

    Call Macro2

End Sub

Here for example the sub would run until line 16.

My question is how can I provide a popup for the

Sub Test ()

to ask the user how many lines of data do you have?

This way the sub would run until that entered number asked by the popup instead of going into VBA and changing that line in the code?

Please let me know, if you can provide a code.

Thank you,
Pinaceous

 

[Joe4]

You could do it like this:

Sub Test()

    Dim i As Integer
    Dim num As Long
        
    On Error GoTo err_chk
    num = InputBox("How many lines of data do you have?")
    
    i = 2
    For i = 2 To num
         Call Macro1
         Call Save_ActSht_as_Pdf
         Application.Wait (Now + TimeValue("0:00:1"))
    Next i

    Call Macro2

    Exit Sub
    
err_chk:
    MsgBox "You have entered an invalid number!", vbOKOnly, "PLEASE TRY AGAIN!"
    
End Sub

But, better yet, can we determine how many lines there are by looking at the sheet?
If so, let us know which column we can look at to find the last row of data.

 

[Pinaceous]

But, better yet, can we determine how many lines there are by looking at the sheet?
If so, let us know which column we can look at to find the last row of data.

Yes you can determine how many lines of data you have by looking at the sheet.

The data sheet that I run the code goes from column A to T.

Thanks again for posting your code!

Cheers,
Pinaceous

 

[Joe4]

Is there is always data in column A, you can do this instead (and not even have to ask for input):

Sub Test()

    Dim i As Integer
    Dim lRow As Long
        
'   Get last row of data in column A
    lRow = Cells(Rows.Count,"A").End(xlUp).Row
    
    For i = 2 To lRow
         Call Macro1
         Call Save_ActSht_as_Pdf
         Application.Wait (Now + TimeValue("0:00:1"))
    Next i

    Call Macro2

End Sub

 

[Pinaceous]

Hey Joe4!

You have just elevated this situation to beyond cool!

That works out really cool.

Cheers!
Pinaceous

 

[Joe4]

You are welcome.
Glad it works for you!