'LOOP THROUGH EACH COMPONENT IN THE ASSEMBLY
For Each recComponent In colAssembly
x = 0
'LOOP THROUGH EACH COMPONENTS SUBCOMPONENTS
For Each subComponent In recComponent.SubComponents
opsRow = x + Range("Operations").Row
matRow = x + 1 + Range("MatAndOS").Row
With Sheets(recComponent.PartNumber)
'SET THE OP TO 82
.Cells(opsRow, Range("op").Column) = 82
'SET SET / UP TIME
.Cells(opsRow, Range("SetupTime").Column).Formula = _
"='" & subComponent.PartNumber & "'!SetupTimeTotal"
'SET / UP COST
.Cells(opsRow, Range("SetupCost").Column).Formula = _
"='" & subComponent.PartNumber & "'!SetupCostTotal"
'LABOR
.Cells(opsRow, Range("LaborCost").Column).Formula = _
"='" & subComponent.PartNumber & "'!LaborCostTotal*'" & subComponent.PartNumber & "'!LotSize"
' 'CHANGE THE CELL STYLE TO CALULCATED
' .Cells(opsRow, Range("LaborCost").Column).Style = "Calculated"
'ADD AN ENTRY IN THE MATERIAL AND OUSIDE SERVICE SECTION THAT
' DOUBLES AS A HYPERLINK TO THE SUBCOMPONENT
.Hyperlinks.Add _
Anchor:=.Cells(matRow, Range("MatAndOS").Column), _
Address:="", _
SubAddress:="'" & subComponent.PartNumber & "'!ActualValue", _
TextToDisplay:="(" & x + 1 & "0) " & subComponent.PartNumber, _
ScreenTip:="Go to sheet " & subComponent.PartNumber
End With
'INCREMENT x AFFECTING opsRow AND matRow
x = x + 1
Next subComponent
Next recComponent
On line 21 you'll notice how I was doing this for an individual cell. Now I want to do it for the whole row of the named range Operations.
.Cells(opsRow, Range("LaborCost").Column).Style = "Calculated"
... from above so it references the row within the named range of Operations?
opsRow = x + Range("Operations").Row
It's a integer that tells me which row i'm on within the named range of Operations. x increments it each pass through the loop.Intersect(.Rows(opsRow), Range("Operations")).Style = "Calculated"
... would be what I'm looking for. Intersect(.Rows(opsRow), .Range("Operations")).Style = "Calculated"
Open in new window
Note, I've used a message box for illustration purposes only, you can put code whatever code you want to execute on the row within the loop.