Within Cell A1, you can see all the data lumped into A1.
[A1] [aaa, bbb, ccc, ddd]
My objective is to get
The following code as below.
‘Andy’s Transpose Coding version 1.0 @ April 18 2012
'Declare all the necessary variables as Variant because tags are alphanumeric and size is difficult to determine.
Dim X, Y, Z As Variant
‘A Input box to ask the user what is the active cell number. E.G If your active cell is in A90, then enter “90” in the prompt.
Z = InputBox("What is the active cell number to transpose?")
'count the number of tags separated by a comma in each cell
Y = Len(Range("E" & Z & "")) - Len(Application.WorksheetFunction.Substitute(Range("E" & Z & ""), ",", ""))
‘A message box to confirm how many tags are found that cell.
'Add rows after finding out how many in the first cell.
Dim i As Integer
For i = 1 To Y
‘A message box to confirm the rows are created.
MsgBox "Rows Created"
'Transposing the data(tags) into new rows
X = Split(Range("E" & Z & "").Value, ",")
Range("E" & Z & "").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)