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
Visual Basic ClassicMicrosoft Excel
Last Comment
Wayne Taylor (webtubbs)
ASKER CERTIFIED SOLUTION
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.
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").
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