How do I paste into 'consecutive' cells?

I want to run a bunch of trials with random numbers. Each trial will have a
single numerical result (in a single cell). I'd like to be able copy the
result value (or use an equality equation) of a trial into a cell somewhere
else, and then run the trial again (new random numbers), and then have the
new result pasted into the NEXT cell below (or next to) the first, and so on,
lots of times, so that I end up with lots of results from my trials. Is
there a way to basically have Excel automatically increment the cell row or
column (I don't care which -- I can grab the data wherever it is) each time I
run a new trial? Sounds a little complicated, but the idea is pretty simple.
I don't want to have to manually copy and paste the "value" of the result
each time -- I'd like to run hundreds or thousands of trials.
--
Thanks.
Warren

The simplest approach is to use a worksheet change event macro. This type of
macro will automatically run every time the worksheet changes. The macro can
detect if your result cell has changed and record this result in the first
available un-used cell in another column.

For your purposes, it's better to use a column since a row can only hold 256
values.
--
Gary's Student


"warren_ds" wrote:

> I want to run a bunch of trials with random numbers. Each trial will have a
> single numerical result (in a single cell). I'd like to be able copy the
> result value (or use an equality equation) of a trial into a cell somewhere
> else, and then run the trial again (new random numbers), and then have the
> new result pasted into the NEXT cell below (or next to) the first, and so on,
> lots of times, so that I end up with lots of results from my trials. Is
> there a way to basically have Excel automatically increment the cell row or
> column (I don't care which -- I can grab the data wherever it is) each time I
> run a new trial? Sounds a little complicated, but the idea is pretty simple.
> I don't want to have to manually copy and paste the "value" of the result
> each time -- I'd like to run hundreds or thousands of trials.
> --
> Thanks.
> Warren

Hi Warren,

Some code something like this:

Range("G10000").End(xlUp).Offset(1, 0).Value = Range("A1").Value

Where A1 is the result value and Column G is where you are listing the
results.

Range("G1").End(xlToRight).Offset(0, 1).Value = Range("A1").Value

Starts in G1 and goes to the right on Row 1.

HTH
Regards,
Howard

"warren_ds" <[email protected]> wrote in message
news:[email protected]...
>I want to run a bunch of trials with random numbers. Each trial will have
>a
> single numerical result (in a single cell). I'd like to be able copy the
> result value (or use an equality equation) of a trial into a cell
> somewhere
> else, and then run the trial again (new random numbers), and then have the
> new result pasted into the NEXT cell below (or next to) the first, and so
> on,
> lots of times, so that I end up with lots of results from my trials. Is
> there a way to basically have Excel automatically increment the cell row
> or
> column (I don't care which -- I can grab the data wherever it is) each
> time I
> run a new trial? Sounds a little complicated, but the idea is pretty
> simple.
> I don't want to have to manually copy and paste the "value" of the result
> each time -- I'd like to run hundreds or thousands of trials.
> --
> Thanks.
> Warren

Another way is to find the first cell to use, then just come down one after you
put the value into that cell.

Dim DestCell as range
dim FromCell as range
dim iCtr as long

with worksheets("Sheet99")
'first open cell in column A (from the bottom, up)
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

set fromcell = worksheets("sheet1").range("a1")
fromcell.formula = "=rand()"

for ictr = 1 to 10
application.calculate
destcell.value = fromcell.value
set destcell = destcell.offset(1,0)
next ictr




warren_ds wrote:
>
> I want to run a bunch of trials with random numbers. Each trial will have a
> single numerical result (in a single cell). I'd like to be able copy the
> result value (or use an equality equation) of a trial into a cell somewhere
> else, and then run the trial again (new random numbers), and then have the
> new result pasted into the NEXT cell below (or next to) the first, and so on,
> lots of times, so that I end up with lots of results from my trials. Is
> there a way to basically have Excel automatically increment the cell row or
> column (I don't care which -- I can grab the data wherever it is) each time I
> run a new trial? Sounds a little complicated, but the idea is pretty simple.
> I don't want to have to manually copy and paste the "value" of the result
> each time -- I'd like to run hundreds or thousands of trials.
> --
> Thanks.
> Warren

--

Dave Peterson