Excel VBA question...Need help..

[Marve]

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

 

[NickHK]

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

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

 

[NickHK]

Marve,
After reading your post again, you can tell the row of the source range that
you are processing with:
Debug.Print r.Row

Not sure what you want to do with it..

NickHK

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

 

[Marve]

Hi Nick,

Thanks a bunch for your replies. I changed my code based on your
suggestions and it looks pretty good and executes quicker. Also thanks
for giving me ideas on how to detect when rows changes on a range of
selected cells on a loop. The reason why I want to know that is because
I'm transposing data based on cells, where a cell have clustered data
like
(0ý8.1ý8.2ý10.1ý10.4ý10.6ý10.8ý10.8ý10.8ý10.8ý10.8ý12ý12).
So on my code I splitted this data on another worksheet and but when it
starts to process a new row range it still posts the data further on
the right instead of starting on a new row in the first blank cell from
top...Here's the code:

Sub BOM_Multiple()
Dim sArray() As String
Dim i As Long, last As Long
Dim n As Integer
Dim r, ActionRange As Range
Dim val As String
Dim t As Long
Dim x, comp As Integer

Const RangeOnly As Long = 8

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

If ActionRange Is Nothing Then
Exit Sub
End If

n = 1
x = 0
comp = 2
For Each r In ActionRange
If r.Row <> comp Then
Range("A1").Select
Do Until IsEmpty(ActiveCell.Offset(x, 0))
x = x + 1
Loop
n = 1
End If
sArray = Split(r & "ý", "ý")
For i = 0 To UBound(sArray)
If sArray(i) <> "" Then
ActiveWorkbook.Worksheets("Transposed").Cells(i + 1
+ x, n).Value = sArray(i)
End If
Next i
comp = r.Row
n = n + 1
Next r

End Sub


I have another question. You see that part of the code when I
initialized the comp variable with a value of 2. How do I make that to
always default on the first row of any selected ranges? Right now I'm
manually changing it based on selected range just so the code works
perfect. Again thanks.

Marve