Sorting table cells in word 2003 Doc

[colleena]

I have a table with 2 columns..

colum 1 contains an aphpa-number ( A503 ) Cust Nr.

colum 2 contains peoples First names seperated by commas some cells have as
many as 10 first names
orher cells might only 1 first name. sorting the table by colum 1 No
problem.....now I need to alpha sort the contents of cell 2. big
problem...any Ideas....

Thanks in advance

 

[Doug Robbins - Word MVP]

The following code in a macro would sort the contents of each cell in the
second column of the first table in the document. It assumes that the
content of the cell in the first row is not to be sorted.

Dim i As Long, Srange As Range, sarray As Variant
Dim source As Document, target As Document, j As Long
Dim sstring As String, rrange As Range
Set source = ActiveDocument
With source.Tables(1).Columns(2)
For i = 2 To .Cells.Count
Set Srange = .Cells(i).Range
Srange.End = Srange.End - 1
Srange = Srange & ","
sarray = Split(Srange)
Set target = Documents.Add
For j = 0 To UBound(sarray)
target.Range.InsertAfter sarray(j) & vbCr
Next j
target.Content.Sort SortOrder:=wdSortOrderAscending
target.Paragraphs(1).Range.Delete
sstring = ""
For j = 1 To target.Range.Paragraphs.Count
Set rrange = target.Range.Paragraphs(j).Range
rrange.End = rrange.End - 1
sstring = sstring & rrange & " "
Next j
sstring = Left(sstring, Len(sstring) - 2)
Srange = sstring
target.Close wdDoNotSaveChanges
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[colleena]

The following code in a macro would sort the contents of each cell in the
second column of the first table in the document. It assumes that the
content of the cell in the first row is not to be sorted.

Dim i As Long, Srange As Range, sarray As Variant
Dim source As Document, target As Document, j As Long
Dim sstring As String, rrange As Range
Set source = ActiveDocument
With source.Tables(1).Columns(2)
For i = 2 To .Cells.Count
Set Srange = .Cells(i).Range
Srange.End = Srange.End - 1
Srange = Srange & ","
sarray = Split(Srange)
Set target = Documents.Add
For j = 0 To UBound(sarray)
target.Range.InsertAfter sarray(j) & vbCr
Next j
target.Content.Sort SortOrder:=wdSortOrderAscending
target.Paragraphs(1).Range.Delete
sstring = ""
For j = 1 To target.Range.Paragraphs.Count
Set rrange = target.Range.Paragraphs(j).Range
rrange.End = rrange.End - 1
sstring = sstring & rrange & " "
Next j
sstring = Left(sstring, Len(sstring) - 2)
Srange = sstring
target.Close wdDoNotSaveChanges
Next i


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

I have a Compile problem with code ???
what am I doing wrong Inamed the macro sortcolum

 

[Doug Robbins - Word MVP]

Sorry,

Looks like I omitted to select the final line of the code when I copied it
into the email message. Here is the complete version:

Sub sortcolumn()
Dim i As Long, Srange As Range, sarray As Variant
Dim source As Document, target As Document, j As Long
Dim sstring As String, rrange As Range
Set source = ActiveDocument
With source.Tables(1).Columns(2)
For i = 2 To .Cells.Count
Set Srange = .Cells(i).Range
Srange.End = Srange.End - 1
Srange = Srange & ","
sarray = Split(Srange)
Set target = Documents.Add
For j = 0 To UBound(sarray)
target.Range.InsertAfter sarray(j) & vbCr
Next j
target.Content.Sort SortOrder:=wdSortOrderAscending
target.Paragraphs(1).Range.Delete
sstring = ""
For j = 1 To target.Range.Paragraphs.Count
Set rrange = target.Range.Paragraphs(j).Range
rrange.End = rrange.End - 1
sstring = sstring & rrange & " "
Next j
sstring = Left(sstring, Len(sstring) - 2)
Srange = sstring
target.Close wdDoNotSaveChanges
Next i
End With
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[colleena]

I have a table with 2 columns..

colum 1 contains an aphpa-number ( A503 ) Cust Nr.

colum 2 contains peoples First names seperated by commas some cells have
as many as 10 first names
orher cells might only 1 first name. sorting the table by colum 1 No
problem.....now I need to alpha sort the contents of cell 2. big
problem...any Ideas....

Thanks in advance
the macro work now ...but

here is and example of column 2 after sort

Gerri,Rochelle,Rein,Annmarie,Tom,Shantaya,Cynthia,Samantha,Janice,Monica

as you can see "cynthia should be first is the cell other cells wrong
too.....

wre close....
thanks

 

[Tony Jollans]

Hi colleena,

The problem with the code for your data appears to be that the Split
statement does not split at commas.

However, you may find this a bit quicker ..

Sub SortNames()

Dim OneCell As Cell, CellRange As Range
Dim SplitVariant, SplitString() As String

For Each OneCell In ActiveDocument.Tables(1).Columns(2).Cells
Set CellRange = OneCell.Range.Duplicate
CellRange.MoveEnd wdCharacter, -1
SplitVariant = Split(CellRange.Text, ",")
SplitString = SplitVariant
WordBasic.SortArray SplitString$
CellRange.Text = Join(SplitString, ",")
Next

End Sub

 

[colleena]

Hi Tony
I get a error 24 when trying to run the code
at WordBasic.SortArray SplitString$
what wrong..????

 

[Tony Jollans]

Hi colleena,

It's probably an empty cell. See if this helps ..

Sub SortNames()

Dim OneCell As Cell, CellRange As Range
Dim SplitVariant, SplitString() As String

For Each OneCell In ActiveDocument.Tables(1).Columns(2).Cells
Set CellRange = OneCell.Range.Duplicate
CellRange.MoveEnd wdCharacter, -1
SplitVariant = Split(CellRange.Text, ",")
SplitString = SplitVariant
If UBound(SplitString) > 0 Then
WordBasic.SortArray SplitString$
CellRange.Text = Join(SplitString, ",")
End If
Next

End Sub