CODEWITHSUNDEEP
excelvbamacros
user6449413
user6449413

Reputation:

How to get column value with known column header name

Say I have a given set of column headers and I know only the column header name. I just want to retrieve the column name e.g I'm searching for the column name "Text" and it is located in cell A, then I want A as my result but its giving me 1. Can anyone help me out.

Here is my code

Sub searchHeader()

columnNamesRow = 1           ' or whichever row your names are in
nameToSearch = "Text"        ' or whatever name you want to search for
columnToUse = 0
lastUsedColumn = Worksheets("Sheet1").Cells(1, Worksheets("Sheet1").Columns.Count).End(xlToLeft).Column

For col = 1 To lastUsedColumn
   If Worksheets("Sheet1").Cells(columnNamesRow, col).Value = nameToSearch Then
      columnToUse = col
   End If
Next col


If columnToUse > 0 Then
' found the column you wanted, do your thing here using "columnToUse" as the column index
MsgBox columnToUse
End If
End Sub

Upvotes: 1

Views: 3578

Answers (2)

user3598756
user3598756

Reputation: 29421

the function @newguy pointed is the answer

anyhow it may help you to consider:

  • use Find() method of Range object instead of looping through cells

  • use the core of that function for your specific needs

like follows:

Option Explicit

Function searchHeader(shtName As String, nametosearch As String, headersRow As Long) As String
    Dim f As Range

    With Worksheets(shtName)
        Set f = .Range(.Cells(headersRow, 1), .Cells(headersRow, .Columns.Count).End(xlToLeft)).Find(what:=nametosearch, LookIn:=xlValues, lookat:=xlWhole)
    End With

    If Not f Is Nothing Then searchHeader = Split(f.Address, "$")(1)
End Function

to be used as follows:

Sub main()

    MsgBox """Text"" is the header of column """ & searchHeader("Sheet1", "Text", 1) & """"

End Sub

Upvotes: 1

brntxr
brntxr

Reputation: 26

Use the number of the column you've obtained, and the row that contains the headers:

Cells(headerRowNumber,colNumber).value

Upvotes: 0

Related Questions