Link to home
Start Free TrialLog in
Avatar of qholmberg
qholmberg

asked on

Select 1 Row Of A Named Range

I have a named range, Operations. It is located on the sheet at I5:N25. I am using VBA to loop through each row of that named range filling formulas that refer to other sheets in the workbook. At the end of each row integration, I would like to change the .style attribute to a style I have defined in the workbook.

This would seem like a simple operation to me but I cannot figure out how to zero in on just the current row I'm of Operations I'm working in within the boundary's of I:N columns. The boundary of I:N will change as time goes on so it generalized so it takes the everything between the left and right outside boundaries.

I can get to the row. I just need the statement(s) that will reference everything in that row within named range.

Thanks.
SOLUTION
Avatar of regmigrant
regmigrant
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie

This code will loop through each row of the named range.
For Each rw In Range("Operations").Rows
    MsgBox rw.Address
Next rw

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.
Avatar of qholmberg

ASKER

I don't have a named range for the row to intersect with. I guess I could and then I would just use the row named range and not need an intersection. If anyone added a row to this area of the spreadsheet, though, they would have to know to name / rename the various affected ranges.

There is a better way ... I'm sure of it.

Here is an example of how I'm accessing the individual cells within that range to populate them ...

    '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

Open in new window

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.
imnore ... I don't want the whole range's style changed. I just want to change the rows where I have inserted calculations that reference other sheets in the workbook. The rest of the range is there for the user to put additional operations. The style change for my stuff let's them know to leave it alone.

So, how do I adapt your loop so it references the 'activerow' so to speak?

Or, how do I adapt my ...
.Cells(opsRow, Range("LaborCost").Column).Style = "Calculated"

Open in new window

... from above so it references the row within the named range of Operations?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hence why I did not post much code in my initial question. There are several named ranges that drive this.

opsrow is defined as ...
opsRow = x + Range("Operations").Row

Open in new window

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.

It would seem to me that ...
Intersect(.Rows(opsRow), Range("Operations")).Style = "Calculated"

Open in new window

... would be what I'm looking for.

And, a slight modification did get it. It needed a '.' in front of Range ...
Intersect(.Rows(opsRow), .Range("Operations")).Style = "Calculated"

Open in new window


Thank you.