help on looping structures
 

This is a database spreadsheet with the first row as the field headers.
I need to get the first Division number and then keep comparing it with the
other division numbers until I hit the next one. If it is the first mention
then I need to insert a gray header row. Can you help me with the loop?


Public Sub ColorRowHeaders()
'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

'Cell Q1 status column ,Take Down, Continue, or Replace
'put a pagebreak at the end of every division

Dim lrow As Long
Dim rng As Range
Dim prevDivsion# As Integer
Dim Division# As Integer
lrow = 2
Set rng = Range("A&lrow")
With rng


Do While lrow < .Cells(Rows.Count, "A").End(xlUp)
Division# = .Cells("0 &lrow")
If .Cells("0 &lrow ").Value < prevDivision
Insert Row
Selection.Interior.ColorIndex = Gray
Header:= .cells(" Department name (cell,P &lrow)


lrow = lrow + 1
End while
End Sub


thanks,

help on looping structures
 

Lots of times, it's much easier to start at the bottom and work your way up the
rows.

Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("Sheet1")
FirstRow = 2
LastRow = .Cells(.Rows.Count, "O").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


Janis wrote:

This is a database spreadsheet with the first row as the field headers.
I need to get the first Division number and then keep comparing it with the
other division numbers until I hit the next one. If it is the first mention
then I need to insert a gray header row. Can you help me with the loop?

Public Sub ColorRowHeaders()
'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

'Cell Q1 status column ,Take Down, Continue, or Replace
'put a pagebreak at the end of every division

Dim lrow As Long
Dim rng As Range
Dim prevDivsion# As Integer
Dim Division# As Integer
lrow = 2
Set rng = Range("A&lrow")
With rng

Do While lrow < .Cells(Rows.Count, "A").End(xlUp)
Division# = .Cells("0 &lrow")
If .Cells("0 &lrow ").Value < prevDivision
Insert Row
Selection.Interior.ColorIndex = Gray
Header:= .cells(" Department name (cell,P &lrow)

lrow = lrow + 1
End while
End Sub

thanks,

--

Dave Peterson

help on looping structures
 

DAve
I appreciate your suggestion. Its hard to grasp 3 loops. THANKS,

"Dave Peterson" wrote:

Lots of times, it's much easier to start at the bottom and work your way up the
rows.

Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

With Worksheets("Sheet1")
FirstRow = 2
LastRow = .Cells(.Rows.Count, "O").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


Janis wrote:

This is a database spreadsheet with the first row as the field headers.
I need to get the first Division number and then keep comparing it with the
other division numbers until I hit the next one. If it is the first mention
then I need to insert a gray header row. Can you help me with the loop?

Public Sub ColorRowHeaders()
'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

'Cell Q1 status column ,Take Down, Continue, or Replace
'put a pagebreak at the end of every division

Dim lrow As Long
Dim rng As Range
Dim prevDivsion# As Integer
Dim Division# As Integer
lrow = 2
Set rng = Range("A&lrow")
With rng

Do While lrow < .Cells(Rows.Count, "A").End(xlUp)
Division# = .Cells("0 &lrow")
If .Cells("0 &lrow ").Value < prevDivision
Insert Row
Selection.Interior.ColorIndex = Gray
Header:= .cells(" Department name (cell,P &lrow)

lrow = lrow + 1
End while
End Sub

thanks,

--

Dave Peterson