Macro Help

[Guest]

Hello,
I am desperately hoping someone can help me with writing a macro. Just to
simplify, let’s say that I have the formula =rand() in cell A1. Let’s say I
want to copy and past the value into cell B1. I want to recalculate and
repeat this process and paste moving to B2, then B3,…. Is this possible? I
can get everything but the “move down one cell and paste†command. I have
been struggling with this for days, so help would be much appreciated!
Thanks!

 

[kemal]

Do you mean something like below ?

Private Sub Worksheet_Activate()
Dim i As Double
For i = 1 To 10 ' from B1 to B10
Me.Range("a1").FormulaR1C1 = "=rand()"
Me.Range("b" & i) = Me.Range("a1").Value
Next i
End Sub

 

[Guest]

Would this do?

Sub doit()
Range("A1").Select
Selection.Copy
For x = 1 To 10
Cells(x, 2).Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Next
End Sub