Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combo Box / Hard Coded List | Excel Discussion (Misc queries) | |||
do the same thing to multiple hard coded cells | Excel Worksheet Functions | |||
hard coded text | Excel Programming | |||
Index Sheet which summarizes all cells with hard coded data. | Excel Programming | |||
Find Hard Coded Cells | Excel Programming |