VBA to find my headers and declare what column they are in?

[tonywatsonhelp]

Hi Everyone,

OK I hope this is clear I know what i want to do so lets see,
I have been looking for a way I can declare the columns my headers are in then use then in any macro instead of always saying "Column D" for example,
I find I end up editing a lot of my macros because i make changes and those changes change the location of a cell or column

So here's what i was thinking.
Please help if this is possible as i have no idea how it could be done but have seen others do similar over the years

I want to some how set it up the my macro finds and stores where my headers for each page are.
I realise we sometimes us the same headers in different sheets but i assume we record the sheet as well as the header name.

So I want something like this,

A place in the VBA editor where I can store columns and cells as names?
For example

Lets say I have a header called "Name" in sheet "Data"

I'd like a macro that goes to Sheet"Data" row 1 and finds the name "Name" , then stores whatever column letter that is (Must be the letter)
If this was written in English it would read like this.
So if "Name Was in Column D"
it would read as
DatNME = the Column letter of: find "Name" in Sheet"Data" row 1,

The when i write a macro in that document Instead of saying Range("A2:A" & lastrow) i could put Range(DatNME & "2:" & DatNME & lastrow) and it would know it was "D"

any ideas help would be greatly apreasiated,

Thanks

Tony

 

[MARK858]

(Must be the letter)

Why when you could just use the Cells Syntax like below as you are converting it to a Variable anyway?

Sub FindChg()
    Dim DatNME As Long, LastRow As Long
  
    LastRow = 100
  
    With Sheets("Data")
        With .Rows(1)
            On Error Resume Next
            DatNME = .Find("Name", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Column
            On Error GoTo 0
        End With
        MsgBox Range(.Cells(2, DatNME), .Cells(LastRow, DatNME)).Address
    End With

End Sub

To get the letter then you would need something like the below (but IMHO you are over complicating it)

Sub FindChg()
    Dim DatNME As String, LastRow As Long
  
    LastRow = 100
  
    With Sheets("Data")
        With .Rows(1)
            On Error Resume Next
            DatNME = Split(.Find("Name", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Address, "$")(1)
            On Error GoTo 0
        End With
        MsgBox .Range(DatNME & "2:" & DatNME & LastRow).Address
    End With

End Sub

 

[tonywatsonhelp]

Thank you mark,
I agree it is over complicating things but I plan to use this in existing codes to make them more robust and they are currently written as "A2" for example so if i can just replace the "A" with the name its nice and quick.

thanks for your help this does what i want but how can i make it excisable in other macros?

Thanks
Tony

 

[MARK858]

You would have to make the variable Public, run the sub then run your new subs (or call the sub from within your new subs).

Option Explicit

Public DatNME As String

Sub FindChg()
 
 
    With Sheets("Data")
        With .Rows(1)
            On Error Resume Next
            DatNME = Split(.Find("Name", .Cells(.Cells.Count), xlValues, , xlByColumns, xlPrevious).Address, "$")(1)
            On Error GoTo 0
        End With
     
    End With

End Sub


Sub test1()
Dim LastRow As Long

LastRow = 100

MsgBox Sheets("Data").Range(DatNME & "2:" & DatNME & LastRow).Address
End Sub

Sub test2()
   MsgBox DatNME
End Sub