I am trying to write a macro that would, depending upon a variable number, determine which row to start the clearing and how many columns to clear. I realize that there are other ways to do this, but I really want to understand why this doesn't work. The error message says "Method 'Range' of Object '_Global' failed". Here is the code:
Sub Eras()
'
' Eras Macro
' Macro recorded 4/18/2002 by Preferred Customer
' Macro to clear a color-filled range; certain number of
' that can vary, and a certain row that can vary (cell B2).
'Establishing array to be able to move a specific column.
Dim B(26) As String
B(1) = "A"
B(2) = "B"
B(3) = "C"
B(4) = "D"
etc - etc
B(15) = "O"
B(16) = "P"
B(17) = "Q"
etc - etc
B(26) = "Z"
' m in this specific case is 14
m = Range("B2").Value
' Establish the range row and width as top of the area
' to be "no filled." For this specific case, the range
' should be D17:Q65536 (to the bottom).
ttt = B(4) & (m + 3)
www = B(m + 3) & 65536
'THE FOLLOWING LINE GIVES ME THE ERROR:
Range("ttt :" & www).Select
Selection.Interior.ColorIndex = xlNone
Range("B2").Select
End Sub
I have tried everything I can think of, including Dimension statements of all kinds and syntax on the problem line. When I step through and view the vlaue of the variables, they have the correct values shown above... then the error causing line. And,if I merely hard code the beginning cell of the range, D17, it works perfectly.
Thanks, SJK.
Sub Eras()
'
' Eras Macro
' Macro recorded 4/18/2002 by Preferred Customer
' Macro to clear a color-filled range; certain number of
' that can vary, and a certain row that can vary (cell B2).
'Establishing array to be able to move a specific column.
Dim B(26) As String
B(1) = "A"
B(2) = "B"
B(3) = "C"
B(4) = "D"
etc - etc
B(15) = "O"
B(16) = "P"
B(17) = "Q"
etc - etc
B(26) = "Z"
' m in this specific case is 14
m = Range("B2").Value
' Establish the range row and width as top of the area
' to be "no filled." For this specific case, the range
' should be D17:Q65536 (to the bottom).
ttt = B(4) & (m + 3)
www = B(m + 3) & 65536
'THE FOLLOWING LINE GIVES ME THE ERROR:
Range("ttt :" & www).Select
Selection.Interior.ColorIndex = xlNone
Range("B2").Select
End Sub
I have tried everything I can think of, including Dimension statements of all kinds and syntax on the problem line. When I step through and view the vlaue of the variables, they have the correct values shown above... then the error causing line. And,if I merely hard code the beginning cell of the range, D17, it works perfectly.
Thanks, SJK.