qholmberg

asked on 5/6/2014

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.

Microsoft ExcelVB Script

Last Comment

qholmberg

8/22/2022 - Mon

SOLUTION

regmigrant

5/6/2014

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

Norie

5/6/2014

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.

qholmberg

5/6/2014

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.

qholmberg

5/6/2014

ASKER

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?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

ASKER CERTIFIED SOLUTION

Norie

5/6/2014

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

qholmberg

5/6/2014

ASKER

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.