asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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