Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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") -- Hope that helps. Vergel Adriano "jfcby" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
Change
MsgBox Range(Cr) to MsgBox Cr if Cr is not a valid cell address or named range then range will not work... -- HTH... Jim Thomlinson "jfcby" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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" wrote in message 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- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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 -- Regards, Tom Ogilvy "jfcby" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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 "Jim Thomlinson" wrote: Change MsgBox Range(Cr) to MsgBox Cr if Cr is not a valid cell address or named range then range will not work... -- HTH... Jim Thomlinson "jfcby" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Method Range of Object Failed, Excel 2000 & 2003
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Method Range of object '_Global' failed | Excel Programming | |||
What does XLS VB Method 'Range of object' _Global failed mean? | Excel Programming | |||
Method 'Range' of object '_Global' failed | Excel Programming | |||
Excel 97 - Clear Method Of Range Object Failed | Excel Programming | |||
Method 'Range' of object '_Global' failed | Excel Programming |