Excel VBA Loop Through Named Ranges

Question

I am trying to loop through a named range in Excel and set its value equal to another named range that is dynamic in value (called SensitivityResults). How do I get Excel to essentially say:

Range("Scenario1").Value = Range("SensitivityResults").Value

Range("Scenario2").Value = Range("SensitivityResults").Value

Range("Scenario2").Value = Range("SensitivityResults").Value

etc...

My code as follows does not work:

Dim i As Integer
Dim s As Integer

For i = 1 To 20
  For s = 1 To 20 
    Range("Active_Scenaro") = s
    Calculate
    Range("Scenario(i)").Value = Range("SensitivityResults").Value
  Next s
Next i

Pᴇʜ · Accepted Answer · 2018-08-13 09:50:00Z

3

You just need to append the counter i as string to your fixed name Scenario:

Range("Scenario" & i).Value = Range("SensitivityResults").Value

answered Aug 13, 2018 at 9:50

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

2 Comments

Reno Over a year ago

very useful thanks. How do I get both loop values, s and i to update at the same time rather than having it run through all the s's and then updating the i's after?

Pᴇʜ Over a year ago

@Reno not exactly sure about what you mean, but: Try to use only one loop (like i loop only and use Range("Active_Scenaro") = i? If this is not what you want please edit your original question, be more specific and give an example.

Collectives™ on Stack Overflow

Excel VBA Loop Through Named Ranges

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related