Method Range of Object Failed, Excel 2000 & 2003

[jfcby]

Hello,

With the following macro code (a work in progress) I'm getting this
error massage: "Method Range of Object Failed" at the following line:
"MsgBox Range(Cr)".

What part of this macro needs to be modified so that I don not get
this error message?

Sub WorkSheetPageSetup()
'Setup WorkSheet PAGE for Zones
'1. Fill ColumnB with color Light Grey - value 001
'2. or Fill ColumnD with color Light Grey - value Left("BLDG")
Dim LastRow As Long
Dim i As Integer
Dim Cr As String
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = 5 To Worksheets.Count
For Each cell In Range("A4:A" & LastRow)
If cell.Offset(, 1).Value = "001" Or Left(cell.Offset(,
3).Value, 4) = "BLDG" Then
Ca = cell.Address
Coa = cell.Offset(, 6).Address
Cr = Chr(34) & Mid(Ca, 2, 1) & Right(Ca, 1) & ":" & Mid(Coa,
2, 1) & Right(Coa, 1) & Chr(34)
MsgBox ActiveWorkbook.Name
MsgBox Chr(34) & Mid(Ca, 2, 1) & Right(Ca, 1) & ":" & Mid(Coa,
2, 1) & Right(Coa, 1) & Chr(34)
MsgBox Range(Cr)
End If
Next cell
Next i
Application.ScreenUpdating = True
End Sub

Thank you for your help,
jfcby

 

[Vergel Adriano]

jfcby,

Before the call to "MsgBox Range(Cr)", inspect the value of Cr. It should
be a valid cell address and should refer to only one cell.

For example, this would work:

msgbox Range("A1")

but not this

msgbox Range("A1:A3")

 

[Jim Thomlinson]

Change
MsgBox Range(Cr)
to
MsgBox Cr

if Cr is not a valid cell address or named range then range will not work...

 

[Jim Cone]

Also, you have too many quotation marks...
Cr = Chr(34) & Mid(Ca, 2, 1) & Right(Ca, 1) & ":" & Mid(Coa, 2, 1) & Right(Coa, 1) & Chr(34)
Should read...
Cr = Mid(ca, 2, 1) & Right(ca, 1) & ":" & Mid(Coa, 2, 1) & Right(Coa, 1)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"jfcby"
Hello,
With the following macro code (a work in progress) I'm getting this
error massage: "Method Range of Object Failed" at the following line:
"MsgBox Range(Cr)".

What part of this macro needs to be modified so that I don not get
this error message?
-snip-

 

[Tom Ogilvy]

try this. I declared CR as range and skipped all the messing around with
string addresses which is counterproductive:

Sub WorkSheetPageSetup()
'Setup WorkSheet PAGE for Zones
'1. Fill ColumnB with color Light Grey - value 001
'2. or Fill ColumnD with color Light Grey - value Left("BLDG")
Dim LastRow As Long
Dim i As Integer
Dim Cr As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
' For i = 5 To Worksheets.Count
For Each cell In Range("A4:A" & LastRow)
If cell.Offset(, 1).Value = "001" Or Left(cell.Offset(, 3).Value, 4) =
"BLDG" Then
Set Cr = cell.Resize(1, 7)
MsgBox Cr.Address(0, 0)
End If
Next cell
' Next i
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

in my test

CR contained "A4:G4"

including the double quotes, so
MsgBox Range(CR) is probably the correct direction, but the OP needs to
remove the doublequotes on each end and display the address property.

Ca = cell.Address(0,0) 'eliminate the $ signs
Coa = cell.Offset(, 6).Address(0,0) ' Eliminate the $ signs
Cr = Ca & ":" & Coa
msgbox Range(CR).Address(0,0)

--
Regards,
Tom Ogilvy

 

[jfcby]

Hello Vergel,

Thank you for your speedy response.

With the information you provided I changed that line to
"Range(cell.Address, cell.Offset(, 6)).Select" and it works fine now.

Thank you for your help,
jfcby

 

[jfcby]

Hello Each Responder,

Vergel Adriano, Jim Thomlinson, Jim Cone, and Tom Ogilvy:

Thank you for your help each response was helpful so that I was able
to use your ideas to get this macro code working:

Sub WorkSheetPageSetup()
'Setup WorkSheet PAGE for Zones
'1. Fill RowB with color Light Grey - value 001 ColumnB
'2. or Fill RowD with color Light Grey - value Left("BLDG")
ColumnD
Dim LastRow As Long
Dim i As Integer
Application.ScreenUpdating = False
For i = 5 To Worksheets.Count
Worksheets(i).Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A4:A" & LastRow)
If cell.Offset(, 1).Text = "001" Or Left(cell.Offset(, 3).Text,
4) = "BLDG" Then
Range(cell, cell.Offset(, 6)).Interior.ColorIndex = 15
End If
Next cell
Next i
Worksheets(5).Select
Application.ScreenUpdating = True
End Sub

Thank you for yor help,
jfcby