Hi

Hi,

I have written the below code but its not working as i wanted.

I have below data in A1 cell (values separated by semi-colon)
A;C;B;M;U

ConvertToColumn() produces output as
A
C
B
M
U

sSortSelection() produces output in ascending order for above output but only when we select that particular range.

generateRow() produces the sorted output as
A;B;C;M;U


But when i run the final() function i am not able to get the correct output. Can anyone please let me know where i am going wrong?

Below is my whole code:


Collapse | Copy Code
Option Explicit

Sub ConvertToColumn()


' constants

Const ksInputWS = "Sheet1"
Const ksInputRange = "A1"
Const ksOutputWS = "Sheet1"
Const ksOutputRange = "B1"
' declarations
Dim rngI As Range, rngO As Range
Dim lRowI As Long, iColI As Integer, lRowO As Long, iColO As Integer
Dim i As Long, J As Long, K As Integer, a As String, b As String
Dim sArray() As String
' start
Set rngI = Worksheets(ksInputWS).Range(ksInputRange)
Set rngO = Worksheets(ksOutputWS).Range(ksOutputRange)
With rngI
lRowI = .Row
iColI = .Column
End With
With rngO
lRowO = .Row
iColO = .Column
.ClearContents
End With


' process
i = lRowI
J = lRowO - 1
With rngI
Do Until .Cells(i, iColI).Value = ""
' row
a = .Cells(i, iColI).Value
' split & fill
sArray = Split(a, ";")
For K = LBound(sArray()) To UBound(sArray())
J = J + 1
rngO.Cells(J, iColO).Value = sArray(K)
Next K
' blank
J = J + 1
rngO.Cells(J, iColO).Value = ""
' cycle
i = i + 1
Loop
End With
' end


Beep
End Sub



Sub generateRow()

Dim i As Integer
Dim s As String

i = 1

Do Until Cells(i, 1).Value = ""
If (s = "") Then
s = Cells(i, 1).Value
Else
s = s & ";" & Cells(i, 1).Value
End If
i = i + 1
Loop

Cells(1, 5).Value = s

End Sub

Public Sub sSortSelection()

'use the keyword "Selection" for the currently selected range (i think the issue is here itself but not sure what to use here instead)
With ActiveSheet.sort
.SortFields.Clear

.SortFields.Add Key:=Selection.Columns(1), Order:=xlAscending
.SetRange Selection
.Apply
End With

End Sub

Sub final()
SplitAndTranspo
sSortSelection
generateRow
End Sub

Thanks in advance

Regards,
Archie