 # Using the mid function in excel vba

How do I extract the range within the following subtotal function:

=SUBTOTAL(9,\$F\$18:\$F\$20)

I want to set rng = Range("F18,F20") using vba in excel.

any ideas? I have been trying the mid function but can't get it to work
Visual Basic ClassicMicrosoft Excel Last Comment
Wayne Taylor (webtubbs)

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Robberbaron (robr)

I have no doubt angels code works but this may be easier to read...

Sub test1()

Dim rf As String, i As Integer, j As Integer
Dim rngtxt As String, rng As Range
rf = Range("b6").Formula        'B6 has the subtotal formula
'=SUBTOTAL(9,\$F\$18:\$F\$20)     complete with \$

'
i = InStr(rf, ",")
If i > 0 Then
'found comma
rngtxt = Mid(rf, i + 1, Len(rf) - 1 - i)

'remove fixed range tags  \$
'rngtxt = Replace(rngtxt, "\$", "")
Set rng = Range(rngtxt)
End If

End Sub
Wayne Taylor (webtubbs)

Hi tiehaze,

I take it you know which cell that formula is in? If so, you can simple use this....

Set rng = Range("A1").Precedents

...assuming A1 contains your formula. Precedents returns a Range object, which can be used as normal.

Also, by Range("F18,F20"), I assumed you meant Range("F18:F20"). If not, use this....

Set rng = Range(Replace(Range("A1").Precedents.Address, ":", ","))

Regards,

Wayne