Question about my Macro

[rwmill9716]

This code takes the value in Cell N2 and writes it to Column S after the last value in that column. I've put a loop into the code to try to get 1,000 values in column S, but what's happening is that it doesn't stop at 1,000 it just keeps pouring data into Column S. I need code that will limit the data in column S to 1,000 values.

Thanks,

Ric Miller

<Code>Private Sub Worksheet_Calculate()
Application.EnableEvents = False
N = 0
Do Until N = 1000
N = N + 1
Range("S" & Rows.Count).End(xlUp).Offset(1) = Range("N2").Value
Application.EnableEvents = True
Loop
End Sub
</Code>

 

[rwmill9716]

I do mean the F9 key, and I don't have a script assigned to it. I've gone into the options/formula page of Excel and changed it to manual.

 

[Dryver14]

This is your code but instead of the second time running it going from 1000 to 2000 it will go from 1 to 1000 again

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
N = 0
Do Until N = 1000
N = N + 1
Range("S" & n).offset(1) = Range("N2").Value
Application.EnableEvents = True
Loop
End Sub