Multiple InputBox Then Split Result Into Cells

[kelly mort]

Hello,
I came up with this code and I am stacked here as to what I wanna achieve.


I need 8 variables; what I have here is just two of those. They are assigned by the inputbox, then after that send them to my worksheet by breaking the input into two where the comma appears in the user input. The first part of the input will be a string data while the second part is number value.
So from what I have here as an example, if I enter say “apple,3” as the input for MyVar1, then cell A1 takes “apple” and cell B1 takes “3”.
Then when I enter say “Mango,34” as the input for MyVar2, then cell C1 takes “Mango” and cell D1 takes “34”, etc


I want someone help pull me out


Blank inputs are accepted but if not blank then all two criteria “label, number” must be there.

Sub InputThings()
    Dim MyVar1, MyVar2
    MyVar1 = InputBox("Enter details like:'Label 1,Number'")
    MyVar2 = InputBox("Enter details like:'Label 2,Number'")
    Sheet2.Range("A1").Value = MyVar1 'take the part before the comma the comma
    Sheet2.Range("B1").Value = MyVar1 'take the part after the comma
    
    Sheet2.Range("C1").Value = MyVar2 'take the part before the comma the comma
    Sheet2.Range("D1").Value = MyVar2 'take the part after the comma
End Sub

 

[My Aswer Is This]

Try something like this.

You can enter 4 values separated by comma in Inputbox
And four values will be entered in sheet2 Row 1
You could enter all 8 values in one Inputbox if you wanted just put comma between values
And modify my script to work with 8 values.

Sub My_Selection_Split_Me()
'Modified  8/10/2018  5:23:20 AM  EDT
Dim ans As String
ans = InputBox("Enter Details")
Dim LString As String
Dim LArray() As String
LString = ans
LArray = Split(LString, ",")
Sheet2.Range("A1").Value = LArray(0)
Sheet2.Range("B1").Value = LArray(1)
Sheet2.Range("C1").Value = LArray(2)
Sheet2.Range("D1").Value = LArray(3)
End Sub

 

[My Aswer Is This]

If you want to enter all 8 values in one Inputbox
Separated by comma
And want all values entered in Row 1 of sheet2

Try this

Sub My_Selection_Split_Me()
'Modified  8/10/2018  5:40:28 AM  EDT
Dim ans As String
ans = InputBox("Enter Details")
Dim LString As String
Dim LArray() As String
LString = ans
LArray = Split(LString, ",")
For i = 1 To 8
    Sheets(2).Cells(1, i).Value = LArray(i - 1)
Next
End Sub

 

[kelly mort]

Great !!!
Thanks a lot

 

[Rick Rothstein]

If you want to enter all 8 values in one Inputbox
Separated by comma
And want all values entered in Row 1 of sheet2

Try this

Sub My_Selection_Split_Me()
'Modified  8/10/2018  5:40:28 AM  EDT
Dim ans As String
ans = InputBox("Enter Details")
Dim LString As String
Dim LArray() As String
LString = ans
LArray = Split(LString, ",")
For i = 1 To [B][COLOR="#FF0000"]UBound(LArray) + 1[/COLOR][/B]
    Sheets(2).Cells(1, i).Value = LArray(i - 1)
Next
End Sub

If you change the upper limit of your For loop to what I show in red above, the code will automatically adapt to whatever number of comma separated variables the user enters into the InputBox.

 

[My Aswer Is This]

Great !!!
Thanks a lot

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[kelly mort]

If you change the upper limit of your For loop to what I show in red above, the code will automatically adapt to whatever number of comma separated variables the user enters into the InputBox.

Okay,

Great!!!!