tiehaze

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

=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

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

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").

Regards,

Wayne

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