need relative cells instead of hard coded
 

This macro works except it leaves off the top row header. I added the code
at the end that hard codes in the first row. The first row is labels so I
start at the 2nd row. In the last piece
it adds the value in cell (2,17) which is the department name to (3,4)
because since I added a blank row header it is now on the 3rd line. The
problem is I need to use this in a loop so I need it to calculate the row it
is on instead of it being hard coded.
So where it says
..rows(2). insert
it needs to be relative using the irow variable.

Thanks, this is urgent!

-----
sDeptID = .Cells(iRow, 16)
sNextDeptID = .Cells(iRow + 1, 16)
'first if block creates the Item Name headers


If .Cells(iRow, 16).Value = .Cells(iRow - 1, 16).Value Then
' if the department is the same as previous
'create the status headers


Else
'if the department is a new department add the row header
sDeptName = .Cells(iRow, 17).Value
.Rows(iRow).Insert
.Range(.Cells(iRow, 1), .Cells(iRow, 26)).Interior.ColorIndex = 15
.Cells(iRow, 3).Value = sDeptName

.Cells(iRow, 3).Font.Bold = True
.Cells(iRow, 3).Font.Size = 14
.Cells(iRow, 3).RowHeight = 18
End If
Next iRow

..Rows(2).Insert
'.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
ActiveSheet.Rows(2).Interior.ColorIndex = 15
..Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15

With .Cells(2, 3)

.Value = "" & .Cells(3, 17)
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18

End With

End With
End Sub

need relative cells instead of hard coded
 

Haven't checked through the whole code, but maybe

..rows(iRow + 1). insert

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Janis" wrote in message
...
This macro works except it leaves off the top row header. I added the
code
at the end that hard codes in the first row. The first row is labels so I
start at the 2nd row. In the last piece
it adds the value in cell (2,17) which is the department name to (3,4)
because since I added a blank row header it is now on the 3rd line. The
problem is I need to use this in a loop so I need it to calculate the row
it
is on instead of it being hard coded.
So where it says
.rows(2). insert
it needs to be relative using the irow variable.

Thanks, this is urgent!

-----
sDeptID = .Cells(iRow, 16)
sNextDeptID = .Cells(iRow + 1, 16)
'first if block creates the Item Name headers


If .Cells(iRow, 16).Value = .Cells(iRow - 1, 16).Value Then
' if the department is the same as previous
'create the status headers


Else
'if the department is a new department add the row header
sDeptName = .Cells(iRow, 17).Value
.Rows(iRow).Insert
.Range(.Cells(iRow, 1), .Cells(iRow, 26)).Interior.ColorIndex = 15
.Cells(iRow, 3).Value = sDeptName

.Cells(iRow, 3).Font.Bold = True
.Cells(iRow, 3).Font.Size = 14
.Cells(iRow, 3).RowHeight = 18
End If
Next iRow

.Rows(2).Insert
'.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15
ActiveSheet.Rows(2).Interior.ColorIndex = 15
.Range(.Cells(2, 1), .Cells(2, 26)).Interior.ColorIndex = 15

With .Cells(2, 3)

.Value = "" & .Cells(3, 17)
.Font.Bold = True
.Font.Size = 14
.RowHeight = 18

End With

End With
End Sub