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

Answer 1

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