Find Cell's Column Given String

Question

I am trying to find the column of a cell which contains the string "Status". The cell is in a different sheet named "Report" and I do not know where the cell is; I only know exactly what it contains. All I need to know is the column that the cell is in. How would I go about doing this (in VBA)? Any help would be greatly appreciated! My code so far is below:

Sheets("Report").Select
Dim header_cell As Variant
Set header_cell = Cells.Find(what:="Status")

Is *Status* in the top row? Is it guaranteed to be there? – May 15 '15 at 20:18 — , May 15 '15 at 20:18
Yes, "Status" is in the top row. – Paradox May 19 '15 at 13:26 — Paradox, May 19 '15 at 13:26

score 3 · Accepted Answer · edited May 23 '17 at 12:21

I would recommend that you avoid activating the worksheet in order to use it.

    Dim header_cell As Range
    With Sheets("Report")
        'this searches just row 1
        'Set header_cell = .Rows(1).Find(what:="Status", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        'this searches the whole worksheet
        Set header_cell = .Cells.Find(what:="Status", lookat:=xlWhole, MatchCase:=False, searchformat:=False)
        Debug.Print header_cell; "Column: " & header_cell.Column
        Debug.Print header_cell; "Address: " & header_cell.Address(0, 0, ReferenceStyle:=xlA1, external:=True)
    End With

See How to avoid using Select in Excel VBA macros for more on methods to get away from relying on select and activate.

score 2 · Answer 2 · answered May 15 '15 at 20:27

As well,

Sub Button1_Click()
    Dim sh As Worksheet, lookRng As Range, c As Range
    Set sh = Sheets("Report")
    Set lookRng = sh.Range("A1:Z1")

    Set c = lookRng.Find(what:="Status", lookat:=xlWhole)

    If Not c Is Nothing Then
        MsgBox c.Address
    Else: MsgBox "Not Found"
        Exit Sub
    End If


End Sub

score 1 · Answer 3 · edited May 18 '15 at 03:52

1

If you create a new variable, say "dim header_cell_column as Integer"; you can do

header_cell_column = Cells.Find(what:="Status").Column

edited May 18 '15 at 03:52

shA.t

16,580
5
54
111

answered May 15 '15 at 20:20

BruceWayne

22,923
15
65
110

1

also `header_cell_column = application.match("status", sheets("Report").rows(1), 0)` – May 15 '15 at 20:43

Find Cell's Column Given String

3 Answers3