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

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

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

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