What I’m trying to do is preserve a cell value generated by a formula for comparison purposes.

So, the formula functions each time the numerical data is entered. The cell containing the formula produces a resulting value. I then reference that value in another cell (which I would like to preserve).

Each time the formula receives new data the resulting value changes. This is fine if I can reference (and preserve) the new value in another cell. This way I would be able to compare the cell values each time new data is entered into the formula.

Unfortunately, other than manually copying and pasting, I have not been able to find a way to automate this simple procedure. Of course, each time the formula sees new data it’s cell value changes as well as any cell that references that value.

Any ideas?

What Im trying to do is preserve a cell value generated by a formula
for comparison purposes.

So, the formula functions each time the numerical data is entered. The
cell containing the formula produces a resulting value. I then
reference that value in another cell (which I would like to preserve).

Each time the formula receives new data the resulting value changes.
This is fine if I can reference (and preserve) the new value in another
cell. This way I would be able to compare the cell values each time new
data is entered into the formula.

Unfortunately, other than manually copying and pasting, I have not been
able to find a way to automate this simple procedure. Of course, each
time the formula sees new data its cell value changes as well as any
cell that references that value.

Any ideas?

Record a macro of the copy/paste? (be sure to PasteValues, though)

Something like...

Range("B6").Value = Range("B6").Value

..where B6 contains the formula that returns the value you want to preserve.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thank you. I'll give it a shot.

Do you think it might also be a good idea to Dimension any variables used as "Static"?

Thank you. I'll give it a shot.

Do you think it might also be a good idea to Dimension any variables
used as "Static"?

If you mean declaring variable type, this is mandatory Best Practice and so you
should check the option 'Require Variable Declaration' on the Options General
tab in the VBA IDE! This will 'force' you to explicitly Define variables and
obviate implicitly using variables altogether!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion