Marve,
Not a grasp what the actual problem is, but..
- You do not need the xlapp stuff, as you are already in Excel.
- Avoid using .Select, unless it is required, as it slows code and makes it
difficult to determine where you are. Mostly it is not necessary.
- Use Application.InputBox (with Type=8) instead, so the user can only input
a range.
- Use the .Value property of the cell, not .FormulaR1C1, as you are not
setting a formula.

Private Sub CommandButton1_Click()
Dim ActionRange As Range
Dim sArray() As String
Dim i As Long
Dim n As Integer
Dim r As Range

Const RangeOnly As Long = 8

On Error Resume Next
Set ActionRange = Application.InputBox("Select the range of data to be
transposed?", , , , , , , RangeOnly)
On Error GoTo 0

If ActionRange Is Nothing Then
Exit Sub
End If

n = 1

For Each r In ActionRange
'Multiple cell ranges
sArray = Split(r, "ý")
For i = 0 To UBound(sArray)
If sArray(i)<"" Then ActiveWorkbook.Worksheets(5).Cells(i + 1,
n).Value = sArray(i)
Next
n = n + 1
Next r

End Sub

NickHK

"Marve" wrote in message
oups.com...
Hello,

I'm trying to transpose clustered data in a cells which values are
separated by this symbol "ý". Sample values in a cell is
"1ý2ý3ý4ý...so on and so forth" I was able to successfully
separate the values to be rows using the ý as delimeter... everything
works great if the range I selected is A1:B1 or B2:C2... transpose data
are from left to right... great.. but when I started have range like
A1:B2.. when I transpose using the code below, it still transfers the
data from left to right instead of putting the A2:B2 on the last
populated row from A1:B1...Is there a way to detect row changed on a
selected range?

This transpose data to this
A1 but when it process range A2 it still left to right instead of
moving from the last row on A1
1
2
3
4

Sub BOM_Multiple()
Dim xlapp As Excel.Application
Dim xlwkb As Excel.Workbook
Dim xlsht As Excel.Sheets

Set xlapp = Excel.Application

Dim sArray() As String
Dim i As Long, last As Long
Dim n As Integer
Dim r As Range
Dim val As String

Start:
val = InputBox("Enter the range of data to be transposed?")
If InStr(val, ":") = 0 Then
MsgBox "Please enter a valid range. E.g. A1:B3."
GoTo Start
End If

n = 1
For Each r In Sheets(5).Range(val).Cells
'Multiple cell ranges
sArray = Split(r & "ý", "ý")
With xlapp
.ActiveWorkbook.Worksheets(4).Select
last = UBound(sArray) - 1
i = 0
Do Until i last
Cells(i + 1, n).FormulaR1C1 = CStr(sArray(i))
i = i + 1
Loop
.ActiveWorkbook.Worksheets(5).Select
End With
n = n + 1
Next r

Set xlapp = Nothing

End Sub