Generate lots of similar numbers (all having the same make-up)  based on a manually entered number using a macro

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

I would like to run a macro that performs the following action.

First I manually enter a product number such as 90-469-51-10 in some cell on the current worksheet.
This number entered before running the macro will always have the following  makeup, i.e.
xx-xxx-51-xx

After running the macro the following numbers are generated automatically one after the other right below the first one that is entered manually (i.e. 90-469-51-10).
Each number generated will be in its own cell (rows).
The variable part of these numbers is the sixth and seventh number (in bold). the other numbers stay always the same
90-469-52-10
90-469-53-10
90-469-54-10
90-469-55-10
90-469-58-10
90-469-59-10
90-469-70-10
90-469-91-10
90-469-71-10
90-469-72-10
90-469-73-10
90-469-74-10
90-469-92-10
90-469-78-10
90-469-75-10
90-469-80-10
90-469-81-10
90-469-82-10
90-469-83-10
90-469-84-10
90-469-87-10
90-469-89-10
90-469-76-10
90-469-78-10
90-469-79-10
90-469-86-10

I came up with a formula solution but for some specific reasons I require a macro solution.

Help is very, very much appreciated. Thank you very much in advance.

Regards, Andreas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Hi Andreas,

How about a BeforeDoubleClick Event?
So that when you double click the cell with manually entered Product Number with a pattern "xx-xxx-xx-xx" (where x is a digit) and the code will ask you about how many Product Numbers you want to generate and once you input how many numbers, the code will generate the Product Numbers and list them below the cell you double clicked.

Does that sound good?

If yes, please place the following code on the Sheet Module in which you want this functionality.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim r               As Long
Dim c               As Long
Dim i               As Long
Dim j               As Long
Dim NumToIncrement  As Long
Dim NextNum         As Long
Dim NumPrefix       As String
Dim NumSuffix       As String
Dim Matches         As Object
Dim HowManyNums     As Long

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "(\d{2}-\d{3}-)(\d{2})(-\d{2})"
    If .Test(Target.Value) Then
        Set Matches = .Execute(Target.Value)
        NumPrefix = Matches(0).submatches(0)
        NumToIncrement = Matches(0).submatches(1)
        NumSuffix = Matches(0).submatches(2)
        
        HowManyNums = Application.InputBox("How many Product Numbers do you want to generate?", "Numbers To Generate!", 10, Type:=1)
        
        If HowManyNums = 0 Then
            MsgBox "You need to enter a number greater than zero.", vbExclamation
            Exit Sub
        End If
        
        Cancel = True
        
        r = Target.Row
        c = Target.Column
        
        For i = r + 1 To HowManyNums + 1
            j = j + 1
            NextNum = NumToIncrement + j
            Cells(i, c) = NumPrefix & NextNum & NumSuffix
            Cells(i, c).Characters(8, Len(CStr(NextNum))).Font.Bold = True
        Next i
    Else
        MsgBox "The cell you double clicked doesn't contain the Product Number with a pattern 'xx-xxx-xx-xx'.", vbExclamation
    End If
End With
End Sub

Open in new window


In the attached, I have entered the Product Number "90-469-51-10" in the cell A1. To test the code, double click the cell A1 to generate the Product Numbers.
GenerateProductNumbers.xlsm
Andreas HermleTeam leader

Author

Commented:
Hi Neeraj, thank you so much for your professional and swift help. As a matter of fact I will not be able to test your solution till tomorrow morning. Again thanks a lot for your kind help 🙂
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
No problem. Please test it as per your convenience.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Andreas HermleTeam leader

Author

Commented:
HI Subodh,

you did a fantastic job on this one, works really great.

BUT regrettably I have to tell you that the numbers I would like to generate are not sequentially for the 6th and 7 digit pair, i.e. 51, 52 etc. They are sometimes sequential and sometimes not at all. I need to generate the list exactly in the order I mentioned in my previous post.

Hope this is feasible and not too much asking. If you wish, I could acknowledge your post and we move this macro requirement over to the paid section. What do you think?

Kind Regards, Andreas

90-469-52-10      
90-469-53-10      
90-469-54-10      
90-469-55-10      
90-469-58-10      
90-469-59-10      
90-469-70-10      
90-469-91-10      
90-469-71-10      
90-469-72-10      
90-469-73-10      
90-469-74-10      
90-469-92-10      
90-469-78-10      
90-469-75-10      
90-469-80-10      
90-469-81-10
90-469-82-10
90-469-83-10
90-469-84-10
90-469-87-10
90-469-89-10
90-469-76-10
90-469-78-10
90-469-79-10
90-469-86-10
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Hi Andreas,

I have sent you a private message with some queries. Please check your inbox.
Andreas HermleTeam leader

Author

Commented:
Great job Neeraj. Thank you very much for it.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Andreas!
Thanks for your feedback!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial