Breaking long sequence of UPCs into separate rows based on user input

[swartzfeger]

All,

I'm working on a fairly complex serialized number project and my formulas in the workbook have worked well so far. The challenge was to increment a sequence with mixed values, ie, ABC00000001. I figured that part out by splitting the fixed alpha prefix ("ABC") from the main integer body to be incremented ("00000001") and defining the fixed length of the integer string ("00000000"). So now, if a user enters the starting sequence number of 100 in their dashboard, the formula =$A$2&TEXT(B2,"00000000") will join [ABC]+[00000]+[100] to = ABC00000100 and increment it correctly. Yay! It works!

Now for the issue:

The user dashboard (Sheet1) asks for a few criteria -- starting sequence number, total numbers in sequence, and then dividing that total sequence into printable "rolls". The dashboard also asks for starting roll number.

Let's say the total numbers in the sequence is 1000, with 100 numbers in each batch. That would give us 10 rolls. Let's say the user defined the starting ROLL number as "19" and the starting SEQUENCE number as "1".

The problem I'm facing:

The user dashboard is Sheet 1. In Sheet 2, I want to create numbered and serialized rows for the user to work from. Based on user-defined input example from above:

Roll Start # End#
19 ABC00000001 ABC00000100
20 ABC00000101 ABC00000200
21 ABC00000201 ABC00000300
... etc... etc...
28 ABC00000901 ABC00001000

I've managed to increment a string with mixed data types, and I've managed to create a CRUDE macro to insert rows into Sheet 2, but I need the rows to be numbered based on user-defined starting roll number (let's say dashboard!A2). The other issue I'm facing is figuring out how to insert these rows beginning with a specified row number (the macro I wrote inserted the correct number of rows, but it inserted them before my column headers).

The second issue -- populating the inserted rows with the starting sequence number and ending sequence number.

So I think I need to dim/declare a rollNumber, startNumber and endNumber

and also say something like startNumber("dashboard!B2")+999 = endNumber

So essentially, Sheet2 would look something like this: (declaring rollNumber as dashboard!A2 and startNumber as dashboard!B2 ???)

SHEET 2
-------------------------------------------------------
1 |A1 |B1 |C1 |
2 |dashboard!A2 |dashboard!B2 |=B2+999 |
3 |=A2+1 |=C2+1 |=B3+999 |
4 | etc... incrementing until number of user-defined rows/rolls are inserted


It's been almost two decades since I messed with vbscript and php so my brain is making a mess of all this, lol!

Thank you so much for any help or pointers!

 

[swartzfeger]

Apologies, I didn't have XL2BB installed at the time and couldn't go back to edit the original post (some formatting was messed up). Here's what I'm trying to accomplish:

UPC-BUILDER.xlsm
	A	B	C	D	E	F	G
1	Order total	Per roll	Total rolls	Actual Run total	Starting Prefix	Starting UPC number	Starting Roll number
2	30000	3000	10	30000	ABC	142501	8
dashboard

Cell Formulas
Range	Formula
C2	C2	=ROUNDUP(A2/B2, 0)
D2	D2	=C2*B2

Sheet1 has the user enter data and the only thing special is the total rolls is rounded up to the next nearest whole number. What I would need in this case is a macro to read the value in C2 and enter that many rows in Sheet2. Also, it would number those rows starting with the user-defined starting roll number in G2. So if there are 10 rolls, 10 rows would be inserted with a roll number, starting with the number in G2 and incremented by +1 through to the final row.

Here's how I would like to pull things in dynamically:

UPC-BUILDER.xlsm
	A	B	C
1	Roll	Start	End
2	8	ABC00142501	ABC00145500
3	9	ABC00145501	ABC00148500
4	10	ABC00148501	ABC00151500
5	11	ABC00151501	ABC00154500
6	12	ABC00154501	ABC00157500
7	13	ABC00157501	ABC00160500
8	14	ABC00160501	ABC00163500
9	15	ABC00163501	ABC00166500
10	16	ABC00166501	ABC00169500
11	17	ABC00169501	ABC00172500
Sheet2

Cell Formulas
Range	Formula
A2	A2	=dashboard!G2
B2	B2	=dashboard!$E$2&TEXT(dashboard!F2,"00000000")
C2	C2	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2-1,"00000000")
A3:A11	A3	=A2+1
B3	B3	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2,"00000000")
C3	C3	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*2-1,"00000000")
B4	B4	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*2,"00000000")
C4	C4	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*3-1,"00000000")
B5	B5	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*3,"00000000")
C5	C5	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*4-1,"00000000")
B6	B6	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*4,"00000000")
C6	C6	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*5-1,"00000000")
B7	B7	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*5,"00000000")
C7	C7	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*6-1,"00000000")
B8	B8	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*6,"00000000")
C8	C8	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*7-1,"00000000")
B9	B9	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*7,"00000000")
C9	C9	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*8-1,"00000000")
B10	B10	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*8,"00000000")
C10	C10	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*9-1,"00000000")
B11	B11	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*9,"00000000")
C11	C11	=dashboard!$E$2&TEXT(dashboard!F2+dashboard!B2*10-1,"00000000")

So something like --
dim perRoll as dashboard!B2
dim totalRolls as dashboard!C2
dim sequenceStart as dashboard!F2
dim rollNumber as dashboard!G2

totalRolls deftermines the number of rows to be inserted, sequenceStart determines the very first UPC, perRoll allows us to determine the end of a range and the beginning sequence of a new roll, and rollNumber gets incremented for each row

I hope these Xl2bb examples help clarify the mess of my original post... thank you!