VBA - copy and paste cells when another cell's value changes

[PCorreia85]

Hi!

I have a formula on a cell (B12) that is based on the value of another cell (B3).

What I need to do is to have a table of 2 columns:
- the first column is a list of values that are supposed to be inserted in cell B3 (say, 500, 501, 502, ... 3000)
- the other column is the results of the cell B12 for each value of the first column.

Does anyone have an idea how can I do this?

Many thanks!
PCorreia

 

[PCorreia85]

hi again,
I've managed to write a code on my own. however this is a bit slow (it's like 3500 iterations).
any ideas on how to make this quicker?

Sub x()
Dim i As Integer
For i = 2 To 3453
Range("b3").Value = Range("n" & (2 + i)).Value
Range("b17").Select
Selection.Copy
Range("o" & (2 + i)).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("b12").Select
Selection.Copy
Range("p" & (2 + i)).Select
Selection.PasteSpecial Paste:=xlPasteValues
Next i


End Sub

 

[MARK858]

Probably a bit faster

Sub x()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To 3453
        Range("b3").Value = Range("n" & (2 + i)).Value
        Range("o" & (2 + i)).Value = Range("b17").Value
        Range("p" & (2 + i)).Value = Range("b12").Value
    Next
    Application.ScreenUpdating = True
End Sub

I am assuming with this that there is a formula in B12 and B17 and they are not fixed numbers.

 

[PCorreia85]

Probably a bit faster

Sub x()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 2 To 3453
        Range("b3").Value = Range("n" & (2 + i)).Value
        Range("o" & (2 + i)).Value = Range("b17").Value
        Range("p" & (2 + i)).Value = Range("b12").Value
    Next
    Application.ScreenUpdating = True
End Sub

I am assuming with this that there is a formula in B12 and B17 and they are not fixed numbers.

many thanks
i'll give it a try