CODEWITHSUNDEEP
vbaexcelexcel-2010
Paradox
Paradox

Reputation: 4566

Find Cell's Column Given String

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")

Upvotes: 1

Views: 78

Answers (3)

BruceWayne
BruceWayne

Reputation: 23283

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

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

Upvotes: 1

user4039065
user4039065

Reputation:

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.

Upvotes: 3

Davesexcel
Davesexcel

Reputation: 6984

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

Upvotes: 2

Related Questions