Convert CSV from clipboard into a transposed list (part solution provided)

[Andibevan]

I am trying to convert a list of comma seperated numbers into a list that is
pasted with each number in an individual cell.

E.g. 125,25,223,23,35,23

would be pasted to

125
25
223
23
35
23

I have managed to convert the CSV into a list of numbers but can only paste
the entire list into 1 cell. Can someone tell me how I would modify it so
that each number is in a single cell.

It will need a reference to the MS Forms library.

Here's what I have so far:-

Sub Test1()
Dim myVar As Variant
Dim MyDataObj As New DataObject

myVar = GetOffClipboard
ClearClipboard

myVar = Replace(myVar, ",", Chr(10), 1)
myVar = Replace(myVar, Chr(13), "", 1)
myVar = Replace(myVar, Chr(10) & Chr(10), Chr(10), 1)
myVar = Replace(myVar, " ", "", 1)
Debug.Print myVar

'Print_Chars (myVar)
MyDataObj.SetText myVar
ActiveCell.Value = myVar
End Sub


TIA Andi

[Tim Williams]

'####################################
Sub tester()

Dim arrvar
Dim myVar As String

myVar = "1,2,3,4,5,6,7,8"

arrvar = Split(myVar, ",")

ActiveCell.Resize(UBound(arrvar) + 1, 1).Value = _
Application.Transpose(arrvar)

End Sub
'##################################
--
Tim Williams
Palo Alto, CA


"Andibevan" <[email protected]> wrote in message news:%[email protected]...
> I am trying to convert a list of comma seperated numbers into a list that is
> pasted with each number in an individual cell.
>
> E.g. 125,25,223,23,35,23
>
> would be pasted to
>
> 125
> 25
> 223
> 23
> 35
> 23
>
> I have managed to convert the CSV into a list of numbers but can only paste
> the entire list into 1 cell. Can someone tell me how I would modify it so
> that each number is in a single cell.
>
> It will need a reference to the MS Forms library.
>
> Here's what I have so far:-
>
> Sub Test1()
> Dim myVar As Variant
> Dim MyDataObj As New DataObject
>
> myVar = GetOffClipboard
> ClearClipboard
>
> myVar = Replace(myVar, ",", Chr(10), 1)
> myVar = Replace(myVar, Chr(13), "", 1)
> myVar = Replace(myVar, Chr(10) & Chr(10), Chr(10), 1)
> myVar = Replace(myVar, " ", "", 1)
> Debug.Print myVar
>
> 'Print_Chars (myVar)
> MyDataObj.SetText myVar
> ActiveCell.Value = myVar
> End Sub
>
>
> TIA Andi
>
>

[Andibevan]

Thanks Tim - that worked great

"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:[email protected]...
> '####################################
> Sub tester()
>
> Dim arrvar
> Dim myVar As String
>
> myVar = "1,2,3,4,5,6,7,8"
>
> arrvar = Split(myVar, ",")
>
> ActiveCell.Resize(UBound(arrvar) + 1, 1).Value = _
> Application.Transpose(arrvar)
>
> End Sub
> '##################################
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Andibevan" <[email protected]> wrote in message
news:%[email protected]...
> > I am trying to convert a list of comma seperated numbers into a list
that is
> > pasted with each number in an individual cell.
> >
> > E.g. 125,25,223,23,35,23
> >
> > would be pasted to
> >
> > 125
> > 25
> > 223
> > 23
> > 35
> > 23
> >
> > I have managed to convert the CSV into a list of numbers but can only
paste
> > the entire list into 1 cell. Can someone tell me how I would modify it
so
> > that each number is in a single cell.
> >
> > It will need a reference to the MS Forms library.
> >
> > Here's what I have so far:-
> >
> > Sub Test1()
> > Dim myVar As Variant
> > Dim MyDataObj As New DataObject
> >
> > myVar = GetOffClipboard
> > ClearClipboard
> >
> > myVar = Replace(myVar, ",", Chr(10), 1)
> > myVar = Replace(myVar, Chr(13), "", 1)
> > myVar = Replace(myVar, Chr(10) & Chr(10), Chr(10), 1)
> > myVar = Replace(myVar, " ", "", 1)
> > Debug.Print myVar
> >
> > 'Print_Chars (myVar)
> > MyDataObj.SetText myVar
> > ActiveCell.Value = myVar
> > End Sub
> >
> >
> > TIA Andi
> >
> >
>
>