Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
application or object defined error
I get a runtime error on line LastRow = .cells(.rows.xxxxxx
I think it is because it doesn't know the wowrksheets belong to the the active workbook object ? Thanks, ------- Public Sub ColorDivHeaders() 'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION 'Database is sorted on Division Number, Department Number and status 'Create row header in light gray above the first row 'of each consecutive Department in a Division 'cell 0 = division # 'cell P = division name 'cell Q = department # 'cell R = department name ' Create a medium grey row header above first row of every status type within a department ' Dim FirstRow As Long Dim LastRow As Long Dim wbk As Workbook Dim iRow As Long Dim wkb As Workbook Set wkb = ActiveWorkbook With Worksheets("Sheet1") FirstRow = 2 LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "o").Value = .Cells(iRow - 1, "o").Value Then 'do nothing Else .Rows(iRow).Insert .Rows(iRow).Interior.ColorIndex = 15 End If Next iRow End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
application or object defined error
The problem is that you have "0" which is zero. Not to sure if you mean to
use "O". Otherwise your column needs to be between 1 and 256 so either... LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row or LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row -- HTH... Jim Thomlinson "Janis" wrote: I get a runtime error on line LastRow = .cells(.rows.xxxxxx I think it is because it doesn't know the wowrksheets belong to the the active workbook object ? Thanks, ------- Public Sub ColorDivHeaders() 'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION 'Database is sorted on Division Number, Department Number and status 'Create row header in light gray above the first row 'of each consecutive Department in a Division 'cell 0 = division # 'cell P = division name 'cell Q = department # 'cell R = department name ' Create a medium grey row header above first row of every status type within a department ' Dim FirstRow As Long Dim LastRow As Long Dim wbk As Workbook Dim iRow As Long Dim wkb As Workbook Set wkb = ActiveWorkbook With Worksheets("Sheet1") FirstRow = 2 LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "o").Value = .Cells(iRow - 1, "o").Value Then 'do nothing Else .Rows(iRow).Insert .Rows(iRow).Interior.ColorIndex = 15 End If Next iRow End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
application or object defined error
The line
LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row should be LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row (Letter O, not zero). Janis wrote: I get a runtime error on line LastRow = .cells(.rows.xxxxxx I think it is because it doesn't know the wowrksheets belong to the the active workbook object ? Thanks, ------- Public Sub ColorDivHeaders() 'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION 'Database is sorted on Division Number, Department Number and status 'Create row header in light gray above the first row 'of each consecutive Department in a Division 'cell 0 = division # 'cell P = division name 'cell Q = department # 'cell R = department name ' Create a medium grey row header above first row of every status type within a department ' Dim FirstRow As Long Dim LastRow As Long Dim wbk As Workbook Dim iRow As Long Dim wkb As Workbook Set wkb = ActiveWorkbook With Worksheets("Sheet1") FirstRow = 2 LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "o").Value = .Cells(iRow - 1, "o").Value Then 'do nothing Else .Rows(iRow).Insert .Rows(iRow).Interior.ColorIndex = 15 End If Next iRow End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
application or object defined error
thanks, I'm starting to get the hang of the cell object range naming.
"Jim Thomlinson" wrote: The problem is that you have "0" which is zero. Not to sure if you mean to use "O". Otherwise your column needs to be between 1 and 256 so either... LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row or LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row -- HTH... Jim Thomlinson "Janis" wrote: I get a runtime error on line LastRow = .cells(.rows.xxxxxx I think it is because it doesn't know the wowrksheets belong to the the active workbook object ? Thanks, ------- Public Sub ColorDivHeaders() 'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION 'Database is sorted on Division Number, Department Number and status 'Create row header in light gray above the first row 'of each consecutive Department in a Division 'cell 0 = division # 'cell P = division name 'cell Q = department # 'cell R = department name ' Create a medium grey row header above first row of every status type within a department ' Dim FirstRow As Long Dim LastRow As Long Dim wbk As Workbook Dim iRow As Long Dim wkb As Workbook Set wkb = ActiveWorkbook With Worksheets("Sheet1") FirstRow = 2 LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "o").Value = .Cells(iRow - 1, "o").Value Then 'do nothing Else .Rows(iRow).Insert .Rows(iRow).Interior.ColorIndex = 15 End If Next iRow End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
application or object defined error
thanks,
"Andrew Taylor" wrote: The line LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row should be LastRow = .Cells(.Rows.Count, "O").End(xlUp).Row (Letter O, not zero). Janis wrote: I get a runtime error on line LastRow = .cells(.rows.xxxxxx I think it is because it doesn't know the wowrksheets belong to the the active workbook object ? Thanks, ------- Public Sub ColorDivHeaders() 'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION 'Database is sorted on Division Number, Department Number and status 'Create row header in light gray above the first row 'of each consecutive Department in a Division 'cell 0 = division # 'cell P = division name 'cell Q = department # 'cell R = department name ' Create a medium grey row header above first row of every status type within a department ' Dim FirstRow As Long Dim LastRow As Long Dim wbk As Workbook Dim iRow As Long Dim wkb As Workbook Set wkb = ActiveWorkbook With Worksheets("Sheet1") FirstRow = 2 LastRow = .Cells(.Rows.Count, "0").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "o").Value = .Cells(iRow - 1, "o").Value Then 'do nothing Else .Rows(iRow).Insert .Rows(iRow).Interior.ColorIndex = 15 End If Next iRow End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
error: ActiveCell.Offset(0, -1).Select = Application-defined or object-defined error | Excel Programming | |||
Macro Run-time Error 1004 Application Defined or Object Defined Error | Excel Programming |