Printing sequential numbering in Descending Order

[yamato1990]

Hello,

I copied macro code from this thread https://www.mrexcel.com/forum/excel-questions/490441-printing-sequential-numbers.html

Sub PrintJobs()
Dim i As Long, startnum As Long, lastnum As Long


    startnum = Application.InputBox("Enter the first job number to be printed", "Print Job Number", 1, , , , , 1)
    lastnum = Application.InputBox("Enter the last job number to be printed", "Print Job Number", 1, , , , , 1)


    For i = startnum To lastnum
        Range("M2").Value = i
        ActiveWindow.SelectedSheets.PrintOut
    Next
    
    End Sub

it works perfectly fine. but is there a way to make this code to print in descending order, for example (100,99,98,97 and so on).

I've try searching in this forum but cannot find the answer as I am not real VBA proficient, so I am looking for a little help.

Thanks.

 

[steve the fish]

Maybe like:

For i = Application.Max(startnum, lastnum) To Application.Min(startnum, lastnum) Step -1

 

[BarryL]

So you want the same code but just the numbers to descend? Can you try the below

Sub PrintJobs()
Dim i As Long, startnum As Long, lastnum As Long


    startnum = Application.InputBox("Enter the first job number to be printed", "Print Job Number", 1, , , , , 1)
    lastnum = Application.InputBox("Enter the last job number to be printed", "Print Job Number", 1, , , , , 1)


    For i = lastnum to startnum Step-1

        Range("M2").Value = i
        ActiveWindow.SelectedSheets.PrintOut
    Next
    
    End Sub

 

[yamato1990]

I've tried both code given and its works fine. Marvelous. although I dont understand the different between Barryl's and Steve's code, or maybe it has same meaning...

well anyway...thanks both of you.

 

[Rick Rothstein]

Here is a non-looping macro that you can also try...

[table="width: 500"]
[tr]
	[td]Sub DescendingSequentialNumbers()
  Dim StartNum As Long, LastNum As Long
  StartNum = Application.InputBox("Enter the first job number to be printed", "Print Job Number", 1, , , , , 1)
  LastNum = Application.InputBox("Enter the last job number to be printed", "Print Job Number", 1, , , , , 1)
  Range("M2").Resize(StartNum - LastNum + 1) = Evaluate(StartNum + LastNum & "-ROW(" & StartNum & ":" & LastNum & ")")
End Sub[/td]
[/tr]
[/table]

 

[yamato1990]

I'm not sure what you mean by non-looping but i've tried it and got the Run-Time error 1004.

 

[Rick Rothstein]

I'm not sure what you mean by non-looping...

Non-looping simply means the code does not use any loops (such as For..Next type loops).

...but i've tried it and got the Run-Time error 1004.

What number did you put in for the start number and what number did you put in for the last number?

 

[yamato1990]

I tried print from number 111 to 120,
so I put start number as 111 and last number 120, and thats the way I use with my current macro.

 

[Rick Rothstein]

I tried print from number 111 to 120,
so I put start number as 111 and last number 120, and thats the way I use with my current macro.

I thought you wanted to list the numbers in descending order? If so, your starting number is the larger number (the first one to be printed) and your last number is the smaller number (the last one to be printed).

 

[Peter_SSs]

Rick, I think that you have missed that the OP wants to put the first number in M2 (& presumably the sheet recalculates something based on that) & then prints the sheet. Then the next number is placed in M2 (not M3) & the sheet printed out etc.
That is what the original code did, and the two codes accepted as working do.