Copy and pasting n times

[Tefleon]

Hi

I'm trying to write a macro for a simple loop and while I think it should be easy, I can't get it right.

On my spreadsheet I have the following two static cells.
E11 - This is my data value
O1 - This has the iteration value

For my macro, I want to take the cell value of E11 and add it to the first blank cell on column H and then repeat this cycle to to the number entered in cell O1.

I should end up with a list of values in column H.

The only conditions are:
1. The results published in cell E11 are from a formula which contains a RAND() command and therefore I need a way to refresh the RAND() after each paste.
2. The paste should be as a conditional VALUE paste rather than just a straight clipboard paste.
3. Column H, which contains the results should be cleared at the start of the macro rather than the new cycle of pastes being appended to the end of an old list.
4. Cell H1 contains a column header and therefore the pastes should start from cell H2.


Thanks in advance for any help you can give with this!

 

[mirabeau]

2. The paste should be as a conditional VALUE paste rather than just a straight clipboard paste.

Not sure what you mean by this. Conditional on what?

But check the following code. It pastes values as it is. If you want the volatile formula in ColumnH then change the .Value in the line near the bottom to .Formula on both sides of the equation.

Sub testcode()
Dim j As Long, nr As Long
Range("H2:H" & Rows.Count).ClearContents
Range("O1") = 7     'as example
Range("E11").Formula = "=randbetween(13,19)" 'as example

For j = 1 To Range("O1").Value
    nr = Range("H" & Rows.Count).End(3).Row + 1
    Calculate
    Range("H" & nr).Value = Range("E11").Value
Next j

End Sub