Trying to create a macro to perform 100 iterations and paste resulting values (2 adjacent row cells) to a 2 x 100 array

Question

I have a worksheet that uses randomly generated numbers in calculations to produce results in two adjacent cells (let's say A1 and A2). I am trying to perform 100 iterations where I'm simply "Calculating Formulas" on the worksheet and then trying to store the results of each iteration next to A1 and A2 (so iteration 1 would be in B1 and B2 and iteration 100 would be in CW1 and CW2). Thanks in advance for your help. Using Excel 2010 if that matters.

Dim Iteration As Integer, i As Integer
Dim val As Variant
Iteration = 100

For i = 1 To Iteration
    Calculate
    Range("A1:A2").Select
    Selection.Copy
    Range("B" & Rows.Count).End(x1Up).Offset(0, 1).PasteSpecial 
    Paste:=xlPasteValues
    Next i
End Sub

Answer 1

I think your major problem was with the location you were selecting for the destination address - you were finding the last unused cell in column B, then shifting over one column (i.e. to column C) and pasting the first set of results. Then you were using that same location for the second set of results, etc.

Sub Test()
    Dim Iteration As Integer, i As Integer
    Dim val As Variant
    Iteration = 100

    'Use a "With" block so that it can be easily changed in the future
    'to refer to a specific sheet if needed
    With ActiveSheet
        For i = 1 To Iteration
            Calculate
            'Determine the last used column on row 1,
            ' offset 1 column to the right,
            ' resize to refer to 2 rows,
            ' set values to the values in A1:A2
            .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Resize(2, 1).Value = .Range("A1:A2").Value
        Next i
    End With
End Sub

---

As pointed out by Steve Lovell, you also had a typo in your original code. It is a good habit to include Option Explicit as the first line in every code module. That will force you to declare all the variables that you use, and the compiler would have highlighted x1Up and given a "Variable not defined" error.