Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a Range Through a Variable
Excel ver: 2003 [11 (SP2)] OS: WXP (SP2) In the following code, VBA returns the error "Method 'Range' of object'_Global' failed" at the statement Range(BorderRange).Select. Code: -------------------- Dim PageBorderRows, TotalBorderCells, RestoreBorderCells Dim str1 As Variant, str2 As Variant, BorderRange PageBorderRows = Array("53", "96") TotalBorderCells = Array("K:L", "V:W", "AG:AH") For Each str1 In PageBorderRows For Each str2 In TotalBorderCells BorderRange = Chr(34) & Mid(str2, 1, (InStr(1, str2, ":") - 1)) & str1 & ":" _ & Mid(str2, (InStr(1, str2, ":") + 1), Len(str2)) & str1 & Chr(34) Range(BorderRange).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next ' TotalBorderCells Next ' PageBorderRows -------------------- How can I create a named range in this manner and use it to select that range? For example, the first pass through the code results in the range "K53:L53". But Range(BorderRange).Select produces the above error. Thanks, - Al -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=490974 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a Range Through a Variable
Change
BorderRange = Chr(34) & Mid(str2, 1, _ (InStr(1, str2, ":") - 1)) & str1 & ":" _ & Mid(str2, (InStr(1, str2, ":") + 1), _ Len(str2)) & str1 & Chr(34) to BorderRange = Mid(str2, 1, _ (InStr(1, str2, ":") - 1)) & str1 & ":" _ & Mid(str2, (InStr(1, str2, ":") + 1), _ Len(str2)) & str1 in otherwords, lose the doublequote on each end. -- Regards, Tom Ogilvy "GoFigure" wrote in message ... Excel ver: 2003 [11 (SP2)] OS: WXP (SP2) In the following code, VBA returns the error "Method 'Range' of object'_Global' failed" at the statement Range(BorderRange).Select. Code: -------------------- Dim PageBorderRows, TotalBorderCells, RestoreBorderCells Dim str1 As Variant, str2 As Variant, BorderRange PageBorderRows = Array("53", "96") TotalBorderCells = Array("K:L", "V:W", "AG:AH") For Each str1 In PageBorderRows For Each str2 In TotalBorderCells BorderRange = Chr(34) & Mid(str2, 1, (InStr(1, str2, ":") - 1)) & str1 & ":" _ & Mid(str2, (InStr(1, str2, ":") + 1), Len(str2)) & str1 & Chr(34) Range(BorderRange).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next ' TotalBorderCells Next ' PageBorderRows -------------------- How can I create a named range in this manner and use it to select that range? For example, the first pass through the code results in the range "K53:L53". But Range(BorderRange).Select produces the above error. Thanks, - Al -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=490974 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a Range Through a Variable
Hi
Try ActiveSheet.Range(BorderRange).Select or Sheets(Sheetname).Range(BorderRange).Select -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "GoFigure" wrote in message ... Excel ver: 2003 [11 (SP2)] OS: WXP (SP2) In the following code, VBA returns the error "Method 'Range' of object'_Global' failed" at the statement Range(BorderRange).Select. Code: -------------------- Dim PageBorderRows, TotalBorderCells, RestoreBorderCells Dim str1 As Variant, str2 As Variant, BorderRange PageBorderRows = Array("53", "96") TotalBorderCells = Array("K:L", "V:W", "AG:AH") For Each str1 In PageBorderRows For Each str2 In TotalBorderCells BorderRange = Chr(34) & Mid(str2, 1, (InStr(1, str2, ":") - 1)) & str1 & ":" _ & Mid(str2, (InStr(1, str2, ":") + 1), Len(str2)) & str1 & Chr(34) Range(BorderRange).Select With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next ' TotalBorderCells Next ' PageBorderRows -------------------- How can I create a named range in this manner and use it to select that range? For example, the first pass through the code results in the range "K53:L53". But Range(BorderRange).Select produces the above error. Thanks, - Al -- GoFigure ------------------------------------------------------------------------ GoFigure's Profile: http://www.excelforum.com/member.php...fo&userid=4274 View this thread: http://www.excelforum.com/showthread...hreadid=490974 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select a Range Through a Variable
Thanks, Tom and Arvi. I just modified the statement as Tom suggested. Does Excel figure out that BorderRange is a string and that's why yo don't have to form a double-quoted string? - A -- GoFigur ----------------------------------------------------------------------- GoFigure's Profile: http://www.excelforum.com/member.php...nfo&userid=427 View this thread: http://www.excelforum.com/showthread.php?threadid=49097 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a variable range | Excel Worksheet Functions | |||
Use a Variable to select a range | Excel Discussion (Misc queries) | |||
How to assign a variable in a range select | Excel Programming | |||
Range("C9:V9").Select ==> changing the 9 to a variable | Excel Worksheet Functions | |||
Range.Select with variable cells ? | Excel Programming |