CODEWITHSUNDEEP
excelvba
Afikasky
Afikasky

Reputation: 133

Loop in Excel VBA and produce output for each iteration

I'm trying to simplify an existing macro recording by creating a loop.

On Sheet1 there is an Active Scenario set to 1 ("F4")

'this is to be manually changed from 1 to 6 by the user

When cell ("F4") is changed a corresponding output in cell ("F8") is produced.

I would like to create a macro that iterates through each scenario (1-6) and then copy and pastes the corresponding output for each scenario in cell ("F8") onto a Sheet2 as a 'special value' without any formula. For example, the output for Scenario 1 relates to cell ("G25") on sheet2.

Do you know how this could be done simply?

This is roughly how I have approach it.

  Dim Scenario As Integer
    Dim Sheet1 As Worksheet
    Dim Sheet2 As Worksheet
    
    Set Sheet1 = ActiveWorkbook.Sheets("Sheet1")
    Set Sheet2 = ActiveWorkbook.Sheets("Sheet2")
    
       Sheet1.Range("F4").Select 'this is where i don't understand how to run through options 1 to 5 in cell F4 
    
For Scenario = 1 To 5
    
        If Scenario.Value = 1 Then
        Sheet1.Range("F8").Copy
        Sheet2.Range("G25").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
        ElseIf i.Value = 2 Then
          Sheet1.Range("F8").Copy
        Sheet2.Range("G25").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Else
        'nothing'
         End If
      Next Cell

Upvotes: 1

Views: 1152

Answers (1)

Jayvee
Jayvee

Reputation: 10875

It seems that you just need to iterate the value of the scenario and then copy, like this:

Sub scenarios()
Dim Scenario As Integer
    Dim Sheet1 As Worksheet
    Dim Sheet2 As Worksheet
    
    Set Sheet1 = ActiveWorkbook.Sheets("Sheet1")
    Set Sheet2 = ActiveWorkbook.Sheets("Sheet2")
    
    For Scenario = 1 To 5
    
        Sheet1.Range("F4").Value = Scenario
    
        Sheet1.Range("F8").Copy

        '
        ' if elif to paste to different destinations according to scenario
        '
        Sheet2.Range("G25").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
      Next
End Sub

Upvotes: 1

Related Questions