Dynamically read in Column

Question

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.

Answer 1

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