Copy and paste cells with random values

user_15314135 0
Viewed 130

My VBA code is:

Sub hello()
Dim i As Long

Sheets("Sheet10").Select

For i = 3 To 101
Cells(i, 21).Value = Cells(2, 21).Value
Cells(i, 22).Value = Cells(2, 22).Value
Sheets("Sheet10").Calculate
Next i

End Sub

Cells(2,21) and Cells(2,22) contain a random formula, it could be =RAND() for simplicity. I would like to:

  1. Copy Cells(2,21) and Cells(2,22)
  2. Paste Cells(2,21) and Cells(2,22) value in the next i row;
  3. Refresh Cells(2,21) and Cells(2,22)
  4. Repeat until i is equal to 101.

What I get instead is that when i=52 the sub miss point 3. and from i=52 to i=101 I get the same values. How can I address this issue?

excel-formula vba excel
2 Answers

Since you seem to be pulling values from other sheets, not just Sheet10, maybe you need to perform a larger recalculation:

Sub hello()

    Dim i As Long
    
    With ThisWorkbook.Sheets("Sheet10")
        For i = 3 To 101
            .Cells(i, 21).Value = .Cells(2, 21).Value
            .Cells(i, 22).Value = .Cells(2, 22).Value
        Next i
        Application.Calculate 'note this affects all open workbooks
    End With 'fixed

End Sub
user_4788840

Columns of Random Values

  • On each iteration, it will write the values of a one-column range (srrg (srrgAddress)) to a row of a 2D one-based array (dData) and recalculate the range containing a 'random-formula'.
  • It will write the values from the array to the worksheet starting with the cell in a row (dfRow) of the first column of the range.
Option Explicit

Sub hello()
    
    Const srrgAddress As String = "U2:V2"
    Const dfRow As Long = 3
    Const dlRow As Long = 101
    
    Dim srrg As Range: Set srrg = Sheet10.Range(srrgAddress)
    Dim cCount As Long: cCount = srrg.Columns.Count
    
    Dim drCount As Long: drCount = dlRow - dfRow + 1
    Dim dData() As Variant: ReDim dData(dfRow To dlRow, 1 To cCount)
    
    Dim dr As Long
    Dim c As Long
    
    For dr = dfRow To dlRow
        For c = 1 To cCount
            dData(dr, c) = srrg.Cells(c).Value
        Next c
        srrg.Calculate
    Next dr
    
    Dim dfCell As Range: Set dfCell = srrg.Cells(1).EntireColumn.Rows(dfRow)
    Dim drg As Range: Set drg = dfCell.Resize(drCount, cCount)
    drg.Value = dData
        
End Sub
user_98140690
Related