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
'##################################

 

[Andibevan]

Thanks Tim - that worked great

'####################################
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
'##################################