macro to select range from active cell range name string
 

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

macro to select range from active cell range name string
 

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" wrote in message
...
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

macro to select range from active cell range name string
 

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.


"aelbob" wrote:

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