Link to home
Create AccountLog in
Avatar of tiehaze
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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
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