Get column index number by column name

[jilo]

Hello,

I have a simple question that i can’t seem to find the answer to. How can I get the column index number from it’s header name, not from A or AA, but from “Category” and such.
My code down here, I simply want to sort by column names instead of hard coding their index.

Sub Srot_l

Dim oSheet1
oSheet1=ThisComponent.CurrentController.getActiveSheet.GetName

if oSheet1 = “List” then’ [‘A’, ‘B’], sort : Name, Type

oSheet = ThisComponent.Sheets.getByName(oSheet1)
oCellrange = oSheet.getCellrangeByName("B1:BA7000")

SortDesc = oCellrange.createSortDescriptor
SortDesc(1).Value = TRUE 'ContainsHeader

Dim l1(0) as New com.sun.star.table.TableSortField
l1(0).Field = 2
l1(0).IsAscending = "True"

SortDesc(3).Value = l1() 'SortFields
oCellrange.sort(SortDesc)

Dim l2(0) as New com.sun.star.table.TableSortField
l2(0).Field = 1
l2(0).IsAscending = "True"

SortDesc(3).Value = l2() 'SortFields
oCellrange.sort(SortDesc)

end if

End Sub

[jilo]

Got an answer already. The code reforged hereafter, using defined range for each cells containing a column name which I use for my sort.

Sub Sort_l

sheet_name = ThisComponent.CurrentController.getActiveSheet.GetName

if sheet_name = “List” then’ sort : Name, Type

	oSheet = ThisComponent.Sheets.getByName(sheet_name)
	oCellrange = oSheet.getCellrangeByName("B1:AB7000")

	decalage = oCellrange.RangeAddress.StartColumn
	num_col_Name = oSheet.getCellRangeByName("Name").cellAddress.Column - decalage
	num_col_Type = oSheet.getCellRangeByName("Type").cellAddress.Column - decalage

	Dim Tri(1) as New com.sun.star.table.TableSortField

	Tri(0).Field = num_col_Name
	Tri(0).IsAscending = "True"

	Tri(1).Field = num_col_Type
	Tri(1).IsAscending = "True"

	SortDesc = oCellrange.createSortDescriptor
	SortDesc(1).Value = TRUE 'ContainsHeader
	SortDesc(3).Value = Tri() 'SortFields

	oCellrange.sort(SortDesc)

end if

End Sub