Excel VBA: create a nested loop and save output for each value in range

Question

I am trying to create a nested loop to input the values in Range("E2:E6") into Cell ("B3") & values in range ("F2:F6") in cell ("B2"). And then record the results from Range("I2:j2") to Sheet2.

This answer (Excel VBA: How to create loop and save output for each value in range?) was a great help in first tackling the problem but I have become stuck as how how I may paste the 25 (5*5) possible resulting values of Range("I2:j2") to Sheet2

Any help much appreciated!

    Sub Nested_Loop()
'
'
'
    
gg = 1

    Dim myRange As Range
    Dim myRange2 As Range
    Dim i As Long, j As Long, h As Long
    
    Worksheets("Sheet1").Activate
    Set myRange = Range("E2:E6")
    Set myRange2 = Range("F2:F6")
    For h = 1 To myRange2.Rows.Count
            For i = 1 To myRange.Rows.Count
                For j = 1 To myRange.Columns.Count
                myRange.Cells(i, j).Select
        Selection.Copy
        Range("B3").Select
        ActiveSheet.Paste
            myRange2.Cells(h, j).Select
        Selection.Copy
        Range("B2").Select
        ActiveSheet.Paste
        Range("I2:j2").Select
        Application.CutCopyMode = False
        Selection.Copy
    Worksheets("Sheet2").Activate
    Worksheets("Sheet2").Cells(i + 1, j + gg).Select     'I want to paste all 25 values (5 possible inputs for each variable(2)). Currently the loop only prints 5 results and then pastes over itself
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Next j
      Worksheets("Sheet1").Activate
   Next i
Worksheets("Sheet1").Activate

Next h

End Sub 

Answer 1

Try to avoid .Select & .Activate. Instead declare wb, ws and rng properly and we can do without copy/paste as well (if you're only interested in values, surely). For guidance, see: How to avoid using Select in Excel VBA. Use rng1.value = rng2.value. It is much faster.

I think that you got a bit confused about how many loops you need. I think you only need one for myRange and a nested one for myRange2 (both on the rows).

Finally, try to use "sensical" variable names; this helps your users (e.g. counter instead of gg).

Below code should hopefully work. (Perhaps a nice challenge to see if you can simply include the calculations that apparently go on in ws.Range("I2:J2") inside the code...)

Sub Nested_Loop()

    Dim wb As Workbook
    Dim ws As Worksheet, ws2 As Worksheet
    
    Dim myRange As Range, myRange2 As Range, destRange As Range
    
    Dim i As Long, j As Long, counter As Long
    
    Set wb = ActiveWorkbook
    
    Set ws = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")
    
    Set myRange = ws.Range("E2:E6")
    Set myRange2 = ws.Range("F2:F6")
    
    'set destination range; "B2" as start inferred from original code
    Set destRange = ws2.Range("B2").Resize(25, 2) 'i.e. .Resize(5*5=25rows,1+1=2cols)
    
    counter = 0
    
    'loop first range = 1 to 5
    For i = 1 To myRange.Rows.Count
        
        'nested -> 5*5
        For j = 1 To myRange2.Rows.Count
        
            'use .value = .value instead of copy/paste
            ws.Range("B3").Value = myRange.Cells(i).Value
            ws.Range("B2").Value = myRange2.Cells(j).Value
            
            'with .value = .value we don't need to leave the activeworksheet
            Range(destRange.Cells(1 + counter, 1), destRange.Cells(1 + counter, 2)).Value = ws.Range("I2:J2").Value
            
            'increment counter to go to next row in destRange
            counter = counter + 1
            
        Next j

    Next i

End Sub

Answer 2

Couple items that could work for you:

dim ws1 as worksheet
set ws1 = thisWorkbook.Sheets(1)
dim ws2 as worksheet
set ws2 = thisWorkbook.Sheets(2)
dim rowNum as long
for rowNum = 1 to 5
    dim colNum as long
    for colNum = 1 to 5
        With ws1
            .Cells(rowNum,colNum).Copy .Cells(2,2)
            .Range(.Cells(2,9),.Cells(2,9+1)).Copy ws2.Range(ws2.Cells(rowNum,colNum*2),ws2.Cells(rowNum,colNum*2+1)
        End With
    next colNum
next rowNum

• Use variables that mean something; make it a habit now, so you're not correcting A, J, K, I, etc., down the road
• Qualify all of your ranges; note how much more complex the destination on ws2 is compared to my With ws1
• I used multiplication on the column number in the paste destination range, which will prevent overwriting data