Sequential Numbering VBA for Multiple Ranges

[smurphy208]

Hello All-

I've created a gift certificate that I would like a VBA to print sequential numbers in three separate ranges (I1, I12, I23).
There are three gift certificates per page each needing it's own number.
I found a VBA that will do one range very well - it prompts me with a beginning and ending numbers however I cannot figure out how to add the other two ranges.

Thanks so much for any help!

Sandy

 

[Joe4]

Welcome to the Board!

Can you post the VBA code that you have that is successfully adding your one range, and maybe we can help you modify it to add the other two as well?

 

[smurphy208]

Thank you!

I love excel and was a member before with my other job and this forum has been super helpful.

Thank you for taking the time to look at this - here is the code:

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


startnum = Application.InputBox("0004", "Print Job Number", 1, , , , , 1)
lastnum = Application.InputBox("0100", "Print Job Number", 1, , , , , 1)


For i = startnum To lastnum
Range("I1,I12").Value = i
ActiveWindow.SelectedSheets.PrintOut

Next


End Sub

 

[Joe4]

So, can you walk me through an example?
What are you entering in for your "startnum" and "lastnum", and then what is it supposed to do, based on those entries?

 

[smurphy208]

Here is the code that I started with:

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


startnum = Application.InputBox("0004", "Print Job Number", 1, , , , , 1)
lastnum = Application.InputBox("0100", "Print Job Number", 1, , , , , 1)


For i = startnum To lastnum
Range("I1,I12").Value = i
ActiveWindow.SelectedSheets.PrintOut

Next


End Sub

I can put in 1 and 6 and it will print 1-6 and change the number in the cell from a 1 to a 6.
It actually brings up a window that ask my starting number and then another window with my ending number.

I'm going to be leaving for an extended weekend with no service just FYI. I really appreciate your help with this!!!
Have a Happy New Year!

 

[Joe4]

So, is the issue that you just this number to appear in all three places instead of those two?

If so, then just change this line:

Range("I1,I12").Value = i[/COLOR]

to this:

[COLOR=#333333]Range("I1,I12,I23").Value = i[/COLOR]

 

[smurphy208]

Hello-
Well I tried this and it prints the same number in all three cells...
I need to have each location (cell) to have a sequential number. Not sure how to accomplish this.
Thanks for your help

 

[Joe4]

Maybe:

Range("I1").Value = i
Range("I12").Value = i+1
Range("I23").Value = i+2

 

[smurphy208]

So very close...so the gift cert is set up with 3 per page. So this code prints the first number as 1 second as 2 third as 3 the next page prints as 2,3,4; next page as 3,4,5; next page as 5,6,7... so it adds the one but not like I'm needing.