macro to select range from active cell range name string

[aelbob]

Objective : An Excel macro that will select (goto) a range specified by
the current active cell, which the user selects from a list of range names
prior to invoking the macro. I found code on the web that will work when the
active cell contains the range in address format (e.g., $A10:$B100), but
not when the content of the active cell is the range name proper (e.g.,
the_goto_range). Using Excel functions in the worksheet, I can create
a list of range addresses in a column next to a column list of range names,
and then use the macro below by selecting the range address for the
desired range to goto -- but shouldn't I be able to get a string range name
into the VBA "Set " range and "Application.Goto Reference:=" statements?
Thanks for any ideas.

Sub go_to_range()
' this macro selects the range indicated by the active cell value
' BUT the active cell range format must be sheet!colrow:colrow --
' range names in active cell do not work !!

Dim SH As Worksheet
Dim arr As Variant
Dim rng As Range

arr = Split(ActiveCell.Value, "!")
' the split function parses the active cell value based on the indicated
delimiter
' (which must be entered in quote marks) & creates a string array

Set SH = Sheets(arr(0))
Set rng = SH.Range(arr(1))
Application.Goto Reference:=rng
End Sub

 

[Rick Rothstein \(MVP - VB\)]

No matter what is going wrong for you with your subroutine (my guess is you
did not use a worksheet reference with your named range, so there was not
"!" to Split on), I do not think you need all the code you posted to do what
you want. This single line macro should do the exact same thing...

Sub go_to_range()
Application.Goto Range(ActiveCell.Value)
End Sub

Rick

 

[aelbob]

Thanks much, rick. Just the simple solution I knew must exist.
Although well versed in macro code used by another spreadsheet,
VBA is brand new to me and rather confusing as I try to piece together
all the statements and syntax. Have not yet found a good text or
manual, so I have been self-learning from the web.
All the examples I had found to select a range from a cell value used the
Application.Goto Reference:= statement which I could not
make work with a string range name in the active cell, but could
make work with an address string.
Thanks again.