PDA

View Full Version : [SOLVED] Proper syntax to sort columns (only identified in variable)



ValerieT
09-02-2013, 01:33 AM
I can't find a proper syntax/way to sorts columns, knowing that the info to identify it is into a variable. Is is possible to "name" a column and use it?

Input data come in different format, and can be in any order. So I can only refer to the column title (always the same, no matter the source) to identify the column number.

For example:

Dim COLNam
DIM COLFirs

For i=1 to 8
If cells(1,i)= "Last Name" then
COLNam=i 'Column LastName number
elseif
If cells(1,i)= "FirstName" then
COLFirs=i 'Column FirstName number
........
next i

Please How can you sort the worksheet using COLNam and COLFir?

TrueTears
09-02-2013, 07:45 AM
What do you want to sort by?

snb
09-02-2013, 09:42 AM
If you use Excel >2003 : select a datacontaining cell,/commandbar insert / Table.

ValerieT
09-03-2013, 02:03 AM
10534

Hello snb
Either I did not explain myself correctly, or I don't know much about VBA, but I don't understand your answer based on my need.
Here is a sample of my macro (please remember I am not a developper so you may find a lot of stuff you would have done better). As all the job is based on comparing data with previous line, I need to ensure the data extraction is sorted the way I want. Except I can use "range A..X" as I don't know where the column is (butI've got the value in a variable)

snb
09-03-2013, 03:57 AM
to sort by columns:


Sub M_snb()
Sheets("Extract").UsedRange.Sort Sheets("Extract").Cells(1), Orientation:=2
End Sub

ValerieT
09-03-2013, 05:05 AM
Hello

It did not worked but made me work and finally it seems to work. Sorry and thanks for the time spent on my ignorance

ActiveWorkbook.Worksheets("Extract").Sort.SortFields.Add Key:=Cells(1, CollVect), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Extract").Sort.SortFields.Add Key:=Cells(1, CollPer), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Extract").Sort.SortFields.Add Key:=Cells(1, CollObj), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Extract").Sort
.SetRange Range("B:I")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

snb
09-03-2013, 07:34 AM
Please, use code tags !!

This should be sufficient:

Sub M_snb()
ActiveWorkbook.sheets("Extract").usedrange.Sort Cells(1, CollVect), ,Cells(1, CollPer), , ,Cells(1, CollObj), ,xlyes
End With

ValerieT
09-04-2013, 12:29 AM
I tried it for a long time, but my syntaxe was always wrong.
Many thanks!

snb
09-04-2013, 01:26 AM
De rien.