Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste into Visible Cells Only | Excel Discussion (Misc queries) | |||
how eliminate zeros in blank cells using paste link | Excel Worksheet Functions | |||
Not able to Paste cells | Excel Discussion (Misc queries) | |||
Summing non consecutive cells | Excel Discussion (Misc queries) | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) |