how to change a cell's value from its formula 1

[Luis939]

Basically I'm trying to change the value of a cell to the value calculated by its formula. So if I have cell A1 = 1 and cell A2 = 2, and if I then have cell A3 =sum("A1","A2&quot, I want to be able change the value of A3 so that it equals only 3, not the formula.
I tried to assign the answer to a variable and paste it to another cell, but if i deleted either A1 or A2 or both, then the cell wouldnt be 3 anymore, and I want to be able to retain that answer

ActiveCell.FormulaR1C1 = "=sum("A1","A2&quot"
Let i = ActiveCell.Value
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1&quot.Select
ActiveCell.Value = i

The new cell has value 3, but once A1 or A3 is deleted then it doesnt work. Thanks

 

[SkipVought]

Hi,
Try this...

1. once the formula calculates, copy it and paste special - values.
Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

THANK YOU SO MUCH!!!!

 

[Zathras]

Have you found the macro recorder? It's a great way to learn how to write the VBA code to do various things. The code it generated looks something like this:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/13/03 by Zathras
'

'
    Range("A3").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
End Sub

You can usually modify the code without too much trouble to fit your specific requirements.

 

[Luis939]

Thank you

 

[SkipVought]

... and Zathras speaks NOT with forked tongue! ;-) Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Luis939]

I have another problem now...I can use the pastespecial method, but for some reason it only works for copy not cut, try it, does anyone think there is any way to make it work for cut?

 

[Zathras]

Not a big problem... copy first, then delete.

 

[RobBroekhuis]

And, as the silliest contribution to this thread, I propose you use:

activecell=activecell

This nicely replaces the formula in the active cell with its value, and looks wonderfully inane.
Rob

 

[Zathras]

Beautiful!