We help IT Professionals succeed at work.

Select 1 Row Of A Named Range

qholmberg
qholmberg asked
on
429 Views
Last Modified: 2014-05-06
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.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.

Author

Commented:
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?
Analyst Assistant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.