VBA Markér alt under overskrift med anden kolonne som reference

sådan som jeg ser det har jeg brug for et offset imellem Alfacell og BravoCell, så kan jeg lave en CharlieCell noget i stil med:


Range("A1").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    ActiveCell.Offset(-1, 0).Activate
    BravoCell = ActiveCell.Address
   
TÆL ANTAL RÆKKER IMELLEM IMELLEM BravoCell og AlfaCell.
    CharlieCell =  BravoceCell.offset (antalle af rækker, o)



    Set NameRange = ActiveSheet.Range(AlfaCell, CharlieCell)

Sub AddKPIMatchogAutoFill()


Dim AlfaCell004 As Variant
Dim Alfacellcol004 As Integer
Dim BravoCell004 As Variant
Dim Bravocellcol004 As Integer
Dim Offsetcount004 As Integer
Dim CharlieCell004 As Variant
Dim SourceRange004 As Variant
Dim FillRange004 As Variant

Worksheets("Ekspo_bunke").Activate


Cells.Find(What:="KPI-Match", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


ActiveCell.Offset(1, 0).Activate

AlfaCell004 = ActiveCell.Address
Alfacellcol004 = ActiveCell.Column
Range("F2").Select

'''''prøver flere varianter herunder med formula
'Selection.Formula = "=INDEKS(KPI;SAMMENLIGN(C2;Typen;0);0)"

ActiveCell.Formula = "=INDEKS(KPI;SAMMENLIGN(C2;Typen;0);0)"
'ActiveCell = "=INDEKS(KPI;SAMMENLIGN(C2;Typen;0);0)"
'ActiveCell.FormulaR1C1 = "=INDEKS(KPI;SAMMENLIGN(C2;Typen;0);0)"



Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Activate

BravoCell004 = ActiveCell.Address
Bravocellcol004 = ActiveCell.Column
Offsetcount004 = Alfacellcol004 - Bravocellcol004



CharlieCell004 = ActiveCell.Offset(0, Offsetcount004).Address

    Set SourceRange004 = ActiveSheet.Range("F2")
    Set FillRange004 = ActiveSheet.Range(AlfaCell004, CharlieCell004)
   
    SourceRange004.AutoFill Destination:=FillRange004



End Sub