在标题下方填充

Option Explicit

Sub FillBelowHeader()
    
    Const hRow As Long = 1
    Const Header As String = "Group"
    Const Formula As String = "=LEFT(RC[-1],4)"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' If the worksheet is filtered, the Find method will fail.
    If ws.AutoFilterMode Then ws.AutoFilterMode = False 
    
    Dim hrg As Range: Set hrg = ws.Rows(hRow)
    
    ' To find the index of the first occurrence of a string
    ' in a one-row or one-column range, you can use 'Application.Match'.
    ' It is case-insensitive.
    Dim hCol As Variant: hCol = Application.Match(Header, hrg, 0)
    If IsError(hCol) Then ' check if there was no match
        MsgBox "Header not found.", vbCritical
        Exit Sub
    End If
    
    Dim lRow As Long
    lRow = ws.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    If lRow = hRow Then ' check if no data below headers
        MsgBox "No data below headers.", vbCritical
        Exit Sub
    End If
    
    hrg.Cells(hCol).Resize(lRow - hRow).Offset(1).FormulaR1C1 = Formula
    ' e.g. hCol is 5 (column 'E') and lRow is 10:
    ' ws.Range("A1:XFD1").Cells(5).Address(0, 0) = "E1"
    ' ws.Range("E1").Resize(10 - 1).Address(0, 0) = "E1:E9"
    ' ws.Range("E1:E9").Offset(1).Address(0, 0) = "E2:E10"
    
End Sub