Macros to generate sequential numbers based on value entered

[Matty Se]

Hello, I have been tasked with using excel to create a macro to auto-populate and generate the next 100 numbers based on values entered, after this is generated I would also need the functionality to be able to hit print and the Sticker Printer Number Convention should be: eg: LP011000000000 ConLP011000000000

What I would like is for the macro to ask the user to enter initial LP and ConLP such as LP040 and ConLP040. Where Reg = Registration and Con = Container etc. and 011 = store number. Then for the macro to auto-populate and generate the next set of 100 numbers based on what was entered for example LP040000000000 and ConLP040000000000, the last generated number would be LP040000000100 and ConLP040000000100

The printout should contain:

LP011000000000 ConLP011000000000

and a scannable barcode

I am a complete beginner at Excel Macros and wouldn't know where to start with this, is this possible to perform using a macro?

 

[DanteAmor]

Hi and welcome to MrExcel!

Type the initial numbers in cells A1 and B1, as shown in the following minisheet:

Dante Amor
	A	B
1	LP040000000000	ConLP040000000000
Sheet1

Run the following macro:

Sub generatesequential()
  Dim i As Long
  Dim s1 As String, s2 As String
  s1 = Split(Range("A1").Value, "P")(1)
  s2 = Split(Range("B1").Value, "P")(1)
  For i = 1 To 100
    Range("A" & i + 1).Value = "LP" & Format(s1 + i, String(Len(s1), "0"))
    Range("B" & i + 1).Value = "ConLP" & Format(s2 + i, String(Len(s2), "0"))
  Next
End Sub

The result in row 2 down:

Dante Amor
	A	B
1	LP040000000000	ConLP040000000000
2	LP040000000001	ConLP040000000001
3	LP040000000002	ConLP040000000002
4	LP040000000003	ConLP040000000003
5	LP040000000004	ConLP040000000004
6	LP040000000005	ConLP040000000005
7	LP040000000006	ConLP040000000006
8	LP040000000007	ConLP040000000007
9	LP040000000008	ConLP040000000008
10	LP040000000009	ConLP040000000009
Sheet1

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (generatesequential) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Alex Blakenburg]

and a scannable barcode

I am not up for this but Mynda Treacy has some non-code options.
https://www.myonlinetraininghub.com/create-barcodes-with-excel-vba

 

[Matty Se]

Hi and welcome to MrExcel!

Type the initial numbers in cells A1 and B1, as shown in the following minisheet:

Dante Amor
	A	B
1	LP040000000000	ConLP040000000000
Sheet1

Run the following macro:

Sub generatesequential()
  Dim i As Long
  Dim s1 As String, s2 As String
  s1 = Split(Range("A1").Value, "P")(1)
  s2 = Split(Range("B1").Value, "P")(1)
  For i = 1 To 100
    Range("A" & i + 1).Value = "LP" & Format(s1 + i, String(Len(s1), "0"))
    Range("B" & i + 1).Value = "ConLP" & Format(s2 + i, String(Len(s2), "0"))
  Next
End Sub

The result in row 2 down:

Dante Amor
	A	B
1	LP040000000000	ConLP040000000000
2	LP040000000001	ConLP040000000001
3	LP040000000002	ConLP040000000002
4	LP040000000003	ConLP040000000003
5	LP040000000004	ConLP040000000004
6	LP040000000005	ConLP040000000005
7	LP040000000006	ConLP040000000006
8	LP040000000007	ConLP040000000007
9	LP040000000008	ConLP040000000008
10	LP040000000009	ConLP040000000009
Sheet1

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (generatesequential) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Hi and welcome to MrExcel!

Type the initial numbers in cells A1 and B1, as shown in the following minisheet:

Dante Amor
	A	B
1	LP040000000000	ConLP040000000000
Sheet1

Run the following macro:

Sub generatesequential()
  Dim i As Long
  Dim s1 As String, s2 As String
  s1 = Split(Range("A1").Value, "P")(1)
  s2 = Split(Range("B1").Value, "P")(1)
  For i = 1 To 100
    Range("A" & i + 1).Value = "LP" & Format(s1 + i, String(Len(s1), "0"))
    Range("B" & i + 1).Value = "ConLP" & Format(s2 + i, String(Len(s2), "0"))
  Next
End Sub

The result in row 2 down:

Dante Amor
	A	B
1	LP040000000000	ConLP040000000000
2	LP040000000001	ConLP040000000001
3	LP040000000002	ConLP040000000002
4	LP040000000003	ConLP040000000003
5	LP040000000004	ConLP040000000004
6	LP040000000005	ConLP040000000005
7	LP040000000006	ConLP040000000006
8	LP040000000007	ConLP040000000007
9	LP040000000008	ConLP040000000008
10	LP040000000009	ConLP040000000009
Sheet1

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (generatesequential) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Awesome you are a legend! Thank you

 

[Matty Se]

I am not up for this but Mynda Treacy has some non-code options.
https://www.myonlinetraininghub.com/create-barcodes-with-excel-vba

Thanks for this!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.