vba - finding column title in row 1 and using it for reference

[borntorun75]

Hi there,

I'm dipping my toes into VBA after many years using Excel. I have a question about referencing a cell - I expect it'll probably be a straight forward reply for those familiar in VBA. However, question aside, I want to see if my approach is sensible or would there be a better way of working.

My macro will be running in a spreadsheet where the column positions will generally be fixed. e.g. In column M, (and specifically cell M1), the text / column heading will be "Production Description". My column headings will always be in row 1.

However, I want to have some flexibility in my VBA referencing where I can find "Production Description" in any of the column headings within the spreadsheet. It's just in case there's any changes in the format of the spreadsheet, (e.g. columns added or dropped), that would otherwise upset the referencing.

The column headings will always be in row 1, but (for the sake of this exercise), the heading will be in any of the columns A to XFD (I'm using Office 2007).

Once I've got the column reference, I'd then continue to reference cells down that column.

Hope I'm making sense (!).

Best regards.
borntorun75

 

[VoG]

See if this helps

Sub atest()
Dim Found As Range, iCol As Long
Set Found = Rows(1).Find(what:="Production Description", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "not found", vbExclamation
Else
    iCol = Found.Column
    MsgBox iCol, vbInformation
End If
End Sub

 

[borntorun75]

Peter,

That's perfect, and helps me a lot.

Thanks for taking the time to reply.

Best regards.