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.

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.

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

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.

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