VBA macro: How to identify column by name (instead of number) before filling

[mike100]

This is a working VBA macro, which fills column 3 with the text "Ind", starting at row 2 and filling as many rows in column 3 as have content in column 1. But I would prefer to identify the column by name (header) instead of number. The column number, as you can see, is 3. The column header is "type". Thank you!

Sub FillInd()
Dim r As Long
Dim c As Long
Dim LR As Long
c = 3
LR = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet
For r = 2 To LR
.Cells(r, c).Value = "Ind"
Next r
End With
End Sub

 

[Micron]

You want to find the column with a particular header because it won't always be the same number? Then perhaps look for the text value that is the header and return the column number where found?

ActiveSheet.Cells.Find("Ind", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).column

 

[Joe4]

Welcome to the Board!

Note that you can skip the whole loop. It is not necessary.
Try this:

Sub FillInd()

    Dim r As Long
    Dim c As Long
    Dim LR As Long
    Dim rng As Range

'   Find last row in first column with data
    LR = Cells(Rows.Count, 1).End(xlUp).Row

'   Find "type" header in row 1
    Set rng = Rows("1:1").Find(What:="type", After:=Range("A1"), LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

'   If found, get column number, otherwise return error message
    If Not rng Is Nothing Then
        c = rng.Column
    Else
        MsgBox "Cannot find the word 'type' in row 1", vbOKOnly, "ERROR!"
        Exit Sub
    End If
        
'   Populate column
    Range(Cells(2, c), Cells(LR, c)).Value = "Ind"
    
End Sub

 

[rpaulson]

c=WorksheetFunction.Match("type", Rows(1), 0)