CODEWITHSUNDEEP
excelvba
Haissam
Haissam

Reputation: 113

Dynamically read in Column

I have a problem. I spent hours designing a form which works just great with all your feedback. Today, everything went wrong. The reason for this is simple. A few new columns got added and, obviously, the data my form is reading in is now wrong.

Thus I was thinking of trying the following...

Rather than using the column number as below

TK = Cells(ActiveCell.Row, "S").Value 'everything in the form refers to the active row

I could possibly use the column headings in Row 1.

Is that possible ? This way the spreadsheet can have columns added up to as many as a user would like and the form would dynamically scan for the right heading and get the column number that way.

My thought is, on opening the form, read in all the headings, pick out the ones I need and assign them to a variable. Then I use my normal code and substitute the variable into the column section.

It sounds easy, but I have no idea how to do this.

Upvotes: 3

Views: 180

Answers (4)

glh
glh

Reputation: 4972

Why use a loop? There's no need to.

Dim col as variant 
Col = application.match("my header", rows(1), 0)
If iserror(col) then
    'not found
Else
    TK = cells(activecell.row, col)
End if

Upvotes: 3

brettdj
brettdj

Reputation: 55682

Use the versatile Find to give you a quick method of detecting where your header is - or if it is missing

Find details here

In the code below I have specified that the search must return

  • an exact match (xlWhole)
  • a case sensitive match (False)

The match can be a partial match (xlPart) if you were looking to match say Game out of Game X

code

Const strFind = "Game"

Sub GetEm()
Dim rng1 As Range
Set rng1 = ActiveSheet.Rows(1).Find(strFind, , xlValues, xlWhole, , , False)
If Not rng1 Is Nothing Then
MsgBox "Your column is " & rng1.Column
Else
MsgBox strFind & " not found", vbCritical
End If
End Sub

Upvotes: 4

MikeD
MikeD

Reputation: 8941

Your thought is a good one. Reading in column headers to calculate addresses is one way to avoid hard coding - e.g.

Sub Test()
Dim R As Range

    Set R = ActiveSheet.[A1]
    Debug.Print ColNo(R, "Col1Hdr")
End Sub

Function ColNo(HdrRange As Range, ColName As String) As Integer
    ' 1st column with empty header is returned if string not found
    ColNo = 1
    Do While HdrRange(1, ColNo) <> ""
        If HdrRange(1, ColNo) = ColName Then Exit Do
        ColNo = ColNo + 1
    Loop
End Function

Another way I frequently use - and I must admit I prefer it over the above, is to define Enum's for all my tables in a seperate "definition" module, e.g.

Public Enum T_VPN   ' sheet VPN
    NofHRows = 3    ' number of header rows
    NofCols = 35    ' number of columns
    MaxData = 203   ' last row validated
    GroupNo = 1
    CtyCode = 2
    Country = 3
    MRegion = 4
    PRegion = 5
    City = 6
    SiteType = 7
    ' ....
End Enum

and use it like

Sub Test1()
    Debug.Print ActiveSheet(T_VPN.NofHRows, T_VPN.Country)
End Sub

As you can see, the usage is simpler. Allthough this is again "some kind" of hardcoding, having all definition in one place reduces maintenance significantly.

Upvotes: 1

Jens Fischer
Jens Fischer

Reputation: 91

For this purpose I usually use a function which runs through the headers (in the first row of a sheet) and returns the number of the column which contains the value I have searched for.

Public Function FindColumn(HeaderName As String, Sht As String) As Long

Dim ColFound As Boolean
Dim StartingPoint As Range

ColFound = False

Set StartingPoint = Sheets(Sht).Range("A1")
Do While StartingPoint.Value <> ""
    If UCase(Trim(StartingPoint.Value)) = UCase(Trim(HeaderName)) Then
        FindColumn = StartingPoint.Column
        ColFound = True
        Exit Do
    Else
        Set StartingPoint = StartingPoint.Offset(0, 1)
    End If
Loop
If Not ColFound Then FindColumn = 0

End Function

Example: If the first row of your sheet named "Timeline" contains headers like e.g. "Date" (A1), "Time" (B1), "Value" (C1) then calling FindColumn("Time", "Timeline") returns 2, since "Time" is the second column in sheet "Timeline"

Hope this may help you a little.

Upvotes: 1

Related Questions