получить нужный адрес ячейки

Sub Sample212122()
    ColNo = 3
    Debug.Print Split(Cells(1, 1).Address, "$")(1)

StartRange = Range("Y3")
StartRange2 = Range("A16")

Set Rng = Selection

Start = Rng.Column
Count = Start + Rng.Count - 1
For Each r In Rng

  If r.MergeCells And Count Mod 3 = 0 Then
'     r.Value = Split(r.Address, "$")(1) & (Count - 1) / 3 + 9
     r.FormulaLocal = "=" & Split(r.Address, "$")(1) & (Count) / 3 + 9
  End If
  
  Count = Count - 1
Next
End Sub



Sub Sample()
    ColNo = 3
    Debug.Print Split(Cells(1, 1).Address, "$")(1)

StartRange = Range("Y3")
StartRange2 = Range("A16")

Set Rng = Range("G3:AA3")
Start = Rng.Column
Count = 21
For Each r In Rng

  If r.MergeCells And Count Mod 3 = 0 Then
'     r.Value = Split(r.Address, "$")(1) & (Count - 1) / 3 + 9
     r.FormulaLocal = "=" & Split(r.Address, "$")(1) & (Count) / 3 + 9
  End If
  
  Count = Count - 1
Next
End Sub


Function dd(rowCell As Range)
  Debug.Print ActiveCell.Address
  dd = Split(ActiveCell.Address, "$")(1)
  dd = ActiveCell.Column
  
End Function