Form number combinations of two from an InputBox entry

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
Hi,
I need a script that takes an InputBox entry separated by commas then form number combinations of two from the data entered. The InputBox will always take a maximum of 5 numbers separated by commas. Example "00,01,02,03,04"

And then form all the combination of two :
"00-01"
"00-02"
"00-03"
"00-04"
"01-02"
"01-03" etc

I want to get the output displayed with a message box or any cool way is okay maybe display it in a listbox or whatever.

I want someone here figure out how to do it for me.
Regards
Kelly
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results in column "B"
Code:
[COLOR=navy]Sub[/COLOR] MG16Aug06
[COLOR=navy]Dim[/COLOR] nstr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nn [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Message [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Title [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Default [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Message = "Enter Numbers seperated by commas"    
Title = "Combinations x 2"
Default = "00,01,02,03,04"
nstr = InputBox(Message, Title, Default)
 
Sp = Split(nstr, ",")
[COLOR=navy]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR=navy]For[/COLOR] nn = n + 1 To UBound(Sp)
        c = c + 1
        Cells(c, 2) = Sp(n) & "," & Sp(nn)
    [COLOR=navy]Next[/COLOR] nn
 [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
Try this for results in column "B"
Code:
[COLOR=navy]Sub[/COLOR] MG16Aug06
[COLOR=navy]Dim[/COLOR] nstr [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] nn [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Message [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Title [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Default [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Message = "Enter Numbers seperated by commas"    
Title = "Combinations x 2"
Default = "00,01,02,03,04"
nstr = InputBox(Message, Title, Default)
 
Sp = Split(nstr, ",")
[COLOR=navy]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR=navy]For[/COLOR] nn = n + 1 To UBound(Sp)
        c = c + 1
        Cells(c, 2) = Sp(n) & "," & Sp(nn)
    [COLOR=navy]Next[/COLOR] nn
 [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
Hi
Hi @MickG,

How do I get three combinations?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,734
Office Version
2016
Platform
Windows
Okay have been able to fix it.

Code:
Sub[/COLOR] MG16Aug06
[COLOR=navy]Dim[/COLOR] nstr [COLOR=navy]As[/COLOR] [COLOR=navy]String,nnn as long, [/COLOR] nn [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Sp [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Message [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Title [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Default [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
Message = "Enter Numbers seperated by commas"    
Title = "Combinations x 2"
Default = "00,01,02,03,04"
nstr = InputBox(Message, Title, Default)
 
Sp = Split(nstr, ",")
[COLOR=navy]For[/COLOR] n = 0 To UBound(Sp)
    [COLOR=navy]For[/COLOR] nn = n + 1 To UBound(Sp)
     [COLOR=navy][FONT=Verdana]For[/FONT][/COLOR][FONT=Verdana] nnn = nn + 1 To UBound(Sp)[/FONT]



        c = c + 1
        Cells(c, 2) = Sp(n) & "," & Sp(nn) & "," & Sp(nnn)

      Next nnn 
    [COLOR=navy]Next[/COLOR] nn
 [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,099,369
Messages
5,468,219
Members
406,574
Latest member
HeinrichPaul

This Week's Hot Topics

Top