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

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

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

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

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