Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND()
I have a formula which calculates different values every time the workbook is
re-calculated, as this formula references a cell which uses the RAND() function. Is it possible to capture each value and have it be populated in a separate table? I'm assuming that, if possible, this entails using a macro, however, if it helps, here's the formula in question: =(SUM(D4:E4,A1:C4)*SUM(A1:E4))^D4 B2 contains the RAND() function. -- Brevity is the soul of wit. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND()
What does B2 have to do with, and how will the formula in question change
just because of a recalculate? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I have a formula which calculates different values every time the workbook is re-calculated, as this formula references a cell which uses the RAND() function. Is it possible to capture each value and have it be populated in a separate table? I'm assuming that, if possible, this entails using a macro, however, if it helps, here's the formula in question: =(SUM(D4:E4,A1:C4)*SUM(A1:E4))^D4 B2 contains the RAND() function. -- Brevity is the soul of wit. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND()
You could log after each calculation. But be aware that there are lots of
calculations that take place while you're changing stuff. I used a table on Sheet2 and saved the value from A1 of Sheet1. This code goes in the Sheet1 module: Option Explicit Private Sub Worksheet_Calculate() Dim DestCell As Range With Worksheets("Sheet2") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Application.EnableEvents = False DestCell.Value = Now DestCell.Offset(0, 1).Value = Me.Range("a1").Value Application.EnableEvents = True End Sub Dave F wrote: I have a formula which calculates different values every time the workbook is re-calculated, as this formula references a cell which uses the RAND() function. Is it possible to capture each value and have it be populated in a separate table? I'm assuming that, if possible, this entails using a macro, however, if it helps, here's the formula in question: =(SUM(D4:E4,A1:C4)*SUM(A1:E4))^D4 B2 contains the RAND() function. -- Brevity is the soul of wit. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND()
B2 is within the range of A1:C4... My question is are you wanting to capture
the value of B2 or the value that the formula is returning or ???. This can be done but it will require some VBA code triggered from the calculate event to copy the values over to the new table... -- HTH... Jim Thomlinson "Bob Phillips" wrote: What does B2 have to do with, and how will the formula in question change just because of a recalculate? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Dave F" wrote in message ... I have a formula which calculates different values every time the workbook is re-calculated, as this formula references a cell which uses the RAND() function. Is it possible to capture each value and have it be populated in a separate table? I'm assuming that, if possible, this entails using a macro, however, if it helps, here's the formula in question: =(SUM(D4:E4,A1:C4)*SUM(A1:E4))^D4 B2 contains the RAND() function. -- Brevity is the soul of wit. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
RAND()
This helps, thanks. I'm only interested in capturing 100 or so values, so it
should take too long to calculate. Dave -- Brevity is the soul of wit. "Dave Peterson" wrote: You could log after each calculation. But be aware that there are lots of calculations that take place while you're changing stuff. I used a table on Sheet2 and saved the value from A1 of Sheet1. This code goes in the Sheet1 module: Option Explicit Private Sub Worksheet_Calculate() Dim DestCell As Range With Worksheets("Sheet2") Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With Application.EnableEvents = False DestCell.Value = Now DestCell.Offset(0, 1).Value = Me.Range("a1").Value Application.EnableEvents = True End Sub Dave F wrote: I have a formula which calculates different values every time the workbook is re-calculated, as this formula references a cell which uses the RAND() function. Is it possible to capture each value and have it be populated in a separate table? I'm assuming that, if possible, this entails using a macro, however, if it helps, here's the formula in question: =(SUM(D4:E4,A1:C4)*SUM(A1:E4))^D4 B2 contains the RAND() function. -- Brevity is the soul of wit. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP with the RAND() Function......AGAIN!!!!! | Excel Worksheet Functions | |||
RAND() Volatility | Excel Discussion (Misc queries) | |||
is there a way to use rand() to select a cell | Excel Discussion (Misc queries) | |||
How to 'freeze' the output generated by RAND()? | Excel Worksheet Functions | |||
Recording incidences of results of a RAND() fucntion. | Excel Discussion (Misc queries) |