how to return column where header value is located...

[sadams1]

I have a large table with many columns that have corresponding headers. Is it possible to search for a specific name in the header row & return it's corresponding column? Using xlookup, vlookup, or hlookup doesn't seem to do this & I've been unable to find any code as an example but a quick data set follows...

A B C
this that other

how to search from anywhere in the table what column "other" belongs to? It would return "C".
Thanks!
Steve

 

[jolivanes]

Sub Maybe()
MsgBox Replace(Replace(Rows(1).Find("Header").Address, "$", ""), "1", "") '<---- Change the word "Header" to the actual Header
End Sub

 

[sadams1]

Try:

Sub Test()
    Dim col As Long
    col = Rows(1).Find("other", LookIn:=xlValues, lookat:=xlWhole).Column
    MsgBox Replace(Cells(1, col).Address(0, 0), "1", "")
End Sub

Quick question...I'm limited in coding so tell me to jump in a lake if this too much but is it much more of a stretch to have this start with a message box to input what to search for? In this case, what to replace "other" with?

 

[jolivanes]

Sub Maybe()
Dim msg As String
msg = InputBox(Prompt:="What is the Header?", Title:="Find Column")
    MsgBox Replace(Replace(Rows(1).Find(msg, , , 1).Address, "$", ""), "1", "")
End Sub

You can do the same for other macros.

 

[sadams1]

Sub Maybe()
Dim msg As String
msg = InputBox(Prompt:="What is the Header?", Title:="Find Column")
    MsgBox Replace(Replace(Rows(1).Find(msg, , , 1).Address, "$", ""), "1", "")
End Sub

You can do the same for other macros.

that is so COOL! many thanks!

 

[Fluff]

Another way

Sub sadams()
   Dim Res As String
   Res = InputBox("Look for")
   MsgBox Split(Range("1:1").Find(Res, , , xlWhole, , , False, , False).Address(1, 0), "$")(0)
End Sub