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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,976
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,343
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
(Must be the letter)
Why when you could just use the Cells Syntax like below as you are converting it to a Variable anyway?
VBA Code:
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)


VBA Code:
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
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,976
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,343
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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).

VBA Code:
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
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,042
Messages
5,767,793
Members
425,435
Latest member
cmardaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top