Sub EFGH()
Dim cell As Range, s As String, s1 As String
Dim iloc As Long
For Each cell In Selection.SpecialCells(xlFormulas)
s = cell.Formula
If InStr(1, s, "Subtotal", vbTextCompare) Then
iloc = InStr(1, s, ":", vbTextCompare)
s1 = Mid(s, iloc + 1, (Len(s) - iloc) - 1)
s = Replace(s, s1, "Above")
cell.Formula = s
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"Sige" wrote:

Hi There,

I would like to replace the cells in "my selection" which contain a
Subtotal-formula
with a new one:

eg:
=SUBTOTAL(9;XXX***:XXX***)
should become
=SUBTOTAL(9;XXX***:Above)

=SUBTOTAL(11;YYY***:YYY***)
should become
=SUBTOTAL(11;YYY***:Above)

Where "Above" is defined name which I create as follows:
ActiveWorkbook.Names.Add Name:="Above", RefersToR1C1:= _
"=INDIRECT(""R[-1]C"",0)"

Thanks for your insight!
Sige