Sequential Numbering VBA for Multiple Ranges

smurphy208

New Member
Joined
Dec 27, 2017
Messages
7
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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:
Code:
Range("I1,I12").Value = i[/COLOR]
to this:
Code:
[COLOR=#333333]Range("I1,I12,I23").Value = i[/COLOR]
 
Upvote 0
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
 
Upvote 0
Maybe:
Code:
Range("I1").Value = i
Range("I12").Value = i+1
Range("I23").Value = i+2
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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