Multiple InputBox Then Split Result Into Cells

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,096
Office Version
  1. 2016
Platform
  1. Windows
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.
Code:
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,069
Office Version
  1. 2013
Platform
  1. Windows
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.




Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
19,069
Office Version
  1. 2013
Platform
  1. Windows
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

Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,020
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Code:
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.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,096
Office Version
  1. 2016
Platform
  1. Windows
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!!!!
 

Forum statistics

Threads
1,171,026
Messages
5,873,371
Members
432,976
Latest member
Manbjorngris

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
Top