Macros to generate sequential numbers based on value entered

Matty Se

New Member
Joined
May 19, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi and welcome to MrExcel!

Type the initial numbers in cells A1 and B1, as shown in the following minisheet:
Dante Amor
AB
1LP040000000000ConLP040000000000
Sheet1


Run the following macro:
VBA Code:
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
AB
1LP040000000000ConLP040000000000
2LP040000000001ConLP040000000001
3LP040000000002ConLP040000000002
4LP040000000003ConLP040000000003
5LP040000000004ConLP040000000004
6LP040000000005ConLP040000000005
7LP040000000006ConLP040000000006
8LP040000000007ConLP040000000007
9LP040000000008ConLP040000000008
10LP040000000009ConLP040000000009
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.
 
Upvote 0
Solution
Hi and welcome to MrExcel!

Type the initial numbers in cells A1 and B1, as shown in the following minisheet:
Dante Amor
AB
1LP040000000000ConLP040000000000
Sheet1


Run the following macro:
VBA Code:
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
AB
1LP040000000000ConLP040000000000
2LP040000000001ConLP040000000001
3LP040000000002ConLP040000000002
4LP040000000003ConLP040000000003
5LP040000000004ConLP040000000004
6LP040000000005ConLP040000000005
7LP040000000006ConLP040000000006
8LP040000000007ConLP040000000007
9LP040000000008ConLP040000000008
10LP040000000009ConLP040000000009
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
AB
1LP040000000000ConLP040000000000
Sheet1


Run the following macro:
VBA Code:
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
AB
1LP040000000000ConLP040000000000
2LP040000000001ConLP040000000001
3LP040000000002ConLP040000000002
4LP040000000003ConLP040000000003
5LP040000000004ConLP040000000004
6LP040000000005ConLP040000000005
7LP040000000006ConLP040000000006
8LP040000000007ConLP040000000007
9LP040000000008ConLP040000000008
10LP040000000009ConLP040000000009
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 :)
 
Upvote 0
Im glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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