Remove formula retain value (VBA)

[ndendrinos]

Hello all,
35 cells in sheet 1 have formulas.
I need to transfer these cells to sheet2 as value only.
The "paste/special value is not an option.


I could use a very long macro to do this such as:

Sub formulatovalue()

    Range("D5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

etc...etc...etc
End Sub

But this sure looks messy.
I know it can be done using (not sure if this is the word for it) an array?
starting like this:

Set MyRange = Range("C5,E5,G5,I5,K5,C8,E8,G8,I8,K8,C10,E10,G10,I10,K10,C12,E12,G12,I12,K12,C14,E14,G14,I14,K14,C16,E16,G16,I16,K16,C18,E18,G18,I18,K18,C20,E20,G20,I20,K20,C22,E22,G22,I22,K22")
     If Not Intersect(Target, MyRange) Is Nothing Then
 With Target

How can I finish the code having it copy the target and paste it to "itself" as value only?

Many thanks

 

[SydneyGeek]

Nick,

If these are the ONLY cells with formulas you could do

Dim i as Integer
Dim c as Range

i=1
For Each c In Cells.SpecialCells(xlCellTypeFormulas)
   Sheets("Sheets2").Cells(i,1) = c.Value
   i=i+1
Next c

Denis

 

[ndendrinos]

Hello Dennis it's been a long time
,
deleting what I assume is an extra "s" in your :

("Sheets2")

and running the code just like you wrote it I get the values that I have in cells E3 & H7 (these are the ones with the formulas copied and pasted in range A1:A2 ... I need them to replace themselves.

in other words : E3.copy ... E3.select ..... PasteSpecial value

I keep reading your code and I cannot understand why I'm having this problem

 

[Brian Richter]

If you want to get rid of the formula, just say sheets(x).cells(row,col)=sheets(x).cells(row,col). where X is the sheet number you want.

If cells(1,3) has a formula of (sum A1:A2), then saying sheets(x).cells(1,3)=sheets(x).cells(1,3) will delete the formula and keep the value

I don't use the cells().select, or copy. It sounds like you created the macro manually with the recorder then tried to edit it. I can see why it is a mess.


Also, you can access the formula of the cells with the Cells(row,col).formulalocal property.

 

[ndendrinos]

Let me rephrase the whole thing

Picture three cells A1,A2&A3
A1=value 10
A2=value 5
B1 has a formula =A1+A2 ... will show as 15

I need to remove the formula and keep the 15 value

If I copy B1 and paste special value again in B1 I will get the desired result.

[B1].Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[SydneyGeek]

Nick, try

Dim c as Range 

For Each c In Cells.SpecialCells(xlCellTypeFormulas) 
   Sheets("Sheet2").Cells(c.Row,c.Column) = c.Value 
Next c

Denis

 

[ndendrinos]

Denis thanks for that it works just great.
It is very late here so until next time AND with Best regards
Nick.

Brian I'm really tired tonight but I will try your suggestion tomorrow and will post again late evening. Thank you.