SpreadsheetGear get column by name?

Question

I'm just starting to work on a project that uses SpreadsheetGear. CopyFromDataTable is used to get all the data from a database. There are a lot of fields to work with; we're formatting them, hiding certain ranges if they're empty, etc. As an example:

SpreadsheetGear.IRange cells = worksheet.Cells;    
cells["G1"].EntireColumn.NumberFormat = "m/d/yyyy";

So if the the columns are rearranged or one is removed, it appears I'd have to go back and adjust all of the hardcoded values to reflect the cell shift. Is there any way to reference the column by its first cell's name to possibly make things more modular?

I found this, but it still requires hardcoding the column.

Answer 1

One possibility is to search for the heading/fieldnames

Sub test()
Dim strFieldName As String
Dim rngHeading As Range

With Sheet1

    strFieldName = "Heading 3" 'find Heading Name
    With .Rows("1:1") 'search row 1 headings

        Set rngHeading = .Find(What:=strFieldName, LookIn:=xlValues, lookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        'check if found
        If Not rngHeading Is Nothing Then

        MsgBox rngHeading.Address
        MsgBox rngHeading.Row
        MsgBox rngHeading.Column
        rngHeading.EntireColumn.NumberFormat = "m/d/yyyy"

        End If

    End With

End With

End Sub