VBA - Parsing out results from inputbox

[jardenp]

I would like to use an input box that allows for users to enter multiple inputs separated by commas and then writes the result to cells on a sheet as text.

For example, if a user enters "364, 212, Tommy, 008" into the InputBox, the result would be:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]364[/TD]
[/TR]
[TR]
[TD]212[/TD]
[/TR]
[TR]
[TD]Tommy[/TD]
[/TR]
[TR]
[TD]008[/TD]
[/TR]
</tbody>[/TABLE]

An entry of "Smith" (no commas involved) would result in:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]

I don't know how many inputs will be included in each entry or else I would just write the full entry to a cell and parse it on the sheet.

Thanks,

Josh in IN

 

[akmatz]

Try this on a COPY of your data NOT the ORIGINAL.
***********************************************************
Sub jardenp()
Dim myString
Dim LenMyString As Integer
Dim CurrentWord, CurrentChar As String
myString = InputBox("enter the values separated by commas")
Range("a2").Select
LenMyString = Len(myString)
CurrentWord = ""
For n = 1 To LenMyString
CurrentChar = Mid(myString, n, 1)
If CurrentChar = "," Then
ActiveCell.Value = CurrentWord
ActiveCell.Offset(1, 0).Select
CurrentWord = ""
Else
CurrentWord = CurrentWord & CurrentChar
End If
Next
ActiveCell.Value = CurrentWord
End Sub
******************************************************************************************

 

[jardenp]

Wow. Count me impressed! That works great.

I don't mean to be picky, but is there a way to paste the values as text? Specifically, so that if any number with leading zeroes is entered, then the leading zeroes would remain?

Thanks!

 

[akmatz]

standby plz

 

[akmatz]

Updated code:
**************************************************************************
Sub jardenp()
Dim myString
Dim LenMyString As Integer
Dim CurrentWord, CurrentChar As String
myString = InputBox("enter the values separated by commas")
Range("a2").Select
LenMyString = Len(myString)
CurrentWord = ""
For n = 1 To LenMyString
CurrentChar = Mid(myString, n, 1)
If CurrentChar = "," Then
ActiveCell.Value = "'" & CurrentWord
ActiveCell.Offset(1, 0).Select
CurrentWord = ""
Else
CurrentWord = CurrentWord & CurrentChar
End If
Next
ActiveCell.Value = "'" & CurrentWord
End Sub
**********************************************************************

 

[jsmath22]

You could just use the Split function.

So say:

sz_npt="Smith, 463, My Dog"
a_npt=split(sz_npt, ", ")

That will turn it into an array:
a_npt(1)="Smith"
a_npt(2)="463"
a_npt(3)="My Dog"

I use it all the time.

-Me

 

[jsmath22]

Then for the text, it's super easy, just go

rng_in=sz_nput & ""

and that will always make it text.

-Me Again

 

[Rick Rothstein]

Here is another macro that will also work...

[table="width: 500"]
[tr]
	[td]Sub SplitInputBoxEntryDown()
  Dim Parts() As String
  On Error GoTo NoText
  Parts = Split(Replace(InputBox("Enter the values separated by commas"), ", ", ","), ",")
  Columns("A").Clear
  With Range("A2").Resize(1 + UBound(Parts))
    .NumberFormat = "@"
    .Cells = Application.Transpose(Parts)
  End With
NoText:
End Sub[/td]
[/tr]
[/table]

 

[jardenp]

Thank you, akmatz for the update (works) and thanks jsmath22 and Rick for the other suggestions. I really appreciate it.

 

[akmatz]

My pleasure. Thanks for using the Forum.