Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
Help: Macro Copy Active Cell to Range Name, loop | Excel Programming | |||
Macro Copy Active Cell to Range Name, loop | Excel Programming | |||
Select a range of columns based on active cell | Excel Programming |