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>

 

[My Aswer Is This]

This is a sheet calculation event script.
Which means every time the sheet calculates the script will run again.
Which may cause the script to enter into a endless loop and will never stop

How do you want to active this script?

 

[rwmill9716]

This is a sheet calculation event script.
Which means every time the sheet calculates the script will run again.
Which may cause the script to enter into a endless loop and will never stop

How do you want to active this script?

Yeah, that's what I'm getting, an endless loop. What I want is 1,000 values, which I can get now by holding down the F9 key.

 

[My Aswer Is This]

You need to consider another way of activating the script.
Like this:

Double click on Range("A1") and the script will run like you want.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Cancel = True
If Target.Cells.Count > 1 Then Exit Sub
Dim N As Long
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 If
End Sub

 

[My Aswer Is This]

Or by double clicking on Range("A1") you could use this script:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("S2:S1000").Value = Range("N2").Value
End If
End Sub

 

[kvsrinivasamurthy]

Try this code. It is faster.

Private Sub Worksheet_Calculate()Application.EnableEvents = False


LR = Range("S" & Rows.Count).End(xlUp).Offset(1).Row


Range("S" & LR & ":S" & LR + 1000 - 1).Value = Range("N2").Value
Application.EnableEvents = True


End Sub

 

[rwmill9716]

I appreciate both of you taking a look at this (kvsrinivasamurthy for some reason I'm getting an error message from your first statement).

While the other code generated data in Column S, it was the same value each time. This is not the case, but you can think of my N2 entry as a random number (Rand()), in which I want to fill Column S with a column of random numbers (I know that I could do this with simply copying =Rand() down Column S, but my model is much more complicated than that). With my original code, I can hit F9 and get a new value added to Column S each time; holding down the F9 button will generate 1,000 data in about 30 sec. I need code that will in effect act like the F9 button has been held down.

Thanks again,

Ric

 

[My Aswer Is This]

My script enters the value from range ("N2")

So if you have "Peter" in Range("N2") Peter will be entered 1000 times.
Not sure what you mean when you say you want a random number.

So you want a Random number is that correct? Then why do you mention Range("N2")

What does "N2" have to do with the script?

 

[rwmill9716]

N2 holds a calculated value from a prediction model that is based on random samples of a production process. Each time I hit the F9 button, the prediction model generates a new estimate in N2. I want to copy 1,000 of these estimates in Column S, then do a histogram of those values. This works well if I just hold down the F9 button until 1,000 estimates are developed (leaving out the loop in my original code). I wondered if I could do the same thing by running a macro.

 

[My Aswer Is This]

I'm not familiar with what happens when you hold down the F9 "Button"

Do you mean F9 Key?

Do you have a script assigned to the F9 Key?