Excel VBA Named Range

Question

I am running a vba code as follows:

Dim score As Integer

SolverReset
score = Range("N3").Value
If score = 1 Then
    SolverLoad loadArea:=Range("N4:N11")
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    SolverReset
    SolverLoad loadArea:=Range("O4:O14")
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    SolverReset
Else:
    SolverReset
    SolverLoad loadArea:=Range("P4:P15")
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
End If

SolverReset
score = Range("N48").Value
If score = 1 Then
    SolverLoad loadArea:=Range("N49:N56")
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    SolverReset
    SolverLoad loadArea:=Range("O49:O59")
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
    SolverReset
Else:
    SolverReset
    SolverLoad loadArea:=Range("P49:P60")
    SolverSolve UserFinish:=True
    SolverFinish KeepFinal:=1
End If

As you can see the first half of the code is repeated again. The range changes from N3 to N48, N4 to N49 and so on.

Now I have to keep repeating the entire code for 172 iterations only changing the range Part which is the cell references to the solver location.

Is there some way I can repeat the process for 172 times using for loop by some how changing the references after each iteration.

All the references increase by +45 each iteration, as is visible.

SJR · Accepted Answer · 2017-05-03 15:10:26Z

1

Try this. It's quite possible I have some of the numbers off but hopefully you get the idea

Sub x()

Dim score As Long, r As Range, i As Long

Set r = Range("N3")

For i = 1 To 172
    SolverReset
    score = r.Value
    If score = 1 Then
        SolverLoad loadArea:=r.Offset(1).Resize(8)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
        SolverReset
        SolverLoad loadArea:=r.Offset(1, 1).Resize(11)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
        SolverReset
    Else:
        SolverReset
        SolverLoad loadArea:=r.Offset(1, 2).Resize(12)
        SolverSolve UserFinish:=True
        SolverFinish KeepFinal:=1
    End If
    Set r = r.Offset(45)
Next i

End Sub

answered May 3, 2017 at 15:10

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sorceri Over a year ago

you could also do step 45 in your for loop

Collectives™ on Stack Overflow

Excel VBA Named Range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related