Set Variable To Header Text Column

Question

I have a workbook that is never received in the same format. To prevent manual intervention, I need to capture the Column that the text employee is in. For example, if the text is in column O - I would execute the below, but I would need the Cells(i,"O") to be changed based off the cell that contains the text employee

Sub DoThis()
Application.ScreenUpdating = False
Dim i As Long
For i = Range("A" & Rows.Count).End(3).Row To 2 Step -1
    If Not IsEmpty(Cells(i, "O").Value) Then 
        'stuff here
    End If
Next i
End Sub

Is the header always just employee? Is that the only text in the cell? — Jordan
– Jordan, Commented Apr 26, 2017 at 13:05
@Jordan - yes the header text I need to locate is always employee — BellHopByDayAmetuerCoderByNigh
– BellHopByDayAmetuerCoderByNigh, Commented Apr 26, 2017 at 13:06

Robin Mackenzie · Accepted Answer · 2017-04-26 13:08:35Z

3

You can use the Find method and get the column of the cell that employee is found in to use in Cells :

Option Explicit

Sub DoThis()

    Dim i As Long
    Dim lngCol As Long

    With Worksheets("Sheet1") '<-- change to your sheet
        lngCol = .Rows(1).Find("employee").Column '<-- assumes header in Row 1
        For i = .Range("A" & .Rows.Count).End(3).Row To 2 Step -1
            If Not IsEmpty(.Cells(i, lngCol).Value) Then
                'stuff here
            End If
        Next i
    End With

End Sub

answered Apr 26, 2017 at 13:08

Robin Mackenzie

19.4k7 gold badges42 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom · Accepted Answer · 2017-04-26 13:07:20Z

1

Use the find method

Cells.Find("employee")

This will find the cell in the range specified (here I've used Cells but I'd narrow this down to your range) and it will return the cell that contains the text "employee". You can then reference this as a Range object i.e. use .Row to get the row number or .Column to get the column number

answered Apr 26, 2017 at 13:07

Tom

9,9184 gold badges34 silver badges51 bronze badges

Collectives™ on Stack Overflow

Set Variable To Header Text Column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related