copying formula to value

[ehsas69]

I have a excel sheet with formula where extract the data from different sheet.Is there anyway set formula back value for certain cells.

Thanks

 

[daniels012]

Do you mean something like:

Range("C4").Value = Range("C4").Value

(This is using VBA code by the way)

Michael

 

[HalfAce]

I'm not sure I understand what you're looking for.
Are you just wanting to convert some formulas to static values?
You can copy them and PasteSpecial as Values.

If you're looking for code to do it, it would look something like this.

With Range("A2:A4")
  .Value = .Value
End With

If it's a non-continuous range. . .

With Range("A2, A4")
  .Value = .Value
End With

If this isn't what you meant then can you explain a bit further?

 

[ehsas69]

yes.Please provide how to use the code.Thanks

 

[HalfAce]

OK, with the workbook open:
To Install the code:
1) Press Alt+F11, then Ctrl+R
2) In the left of the two windows that will open, locate the name of your workbook and
click on it once.
3) From the menu, choose Insert and then Module.
4) Copy the code you wish to use from below and paste it into the white area on
the right that is the module you just inserted.
(Note, you will only need one or the other, but not both.)

Sub ConvertContinuousRange()
'convert the entire range A2 to A4
  With Range("A2:A4")
    .Value = .Value
  End With
End Sub


Sub ConvertNonContinuousRange()
'convert just the cells A2 and A4
  With Range("A2, A4")
    .Value = .Value
  End With
End Sub

5) Change the range(s) referenced in the code to the range(s) you're interested in.
6) Press Alt+Q to get back to your worksheet.

Now, to run the code:
1) With the worksheet containing the formulas you wish to convert being the active sheet,
press Alt+F8
2) select the name of the macro you installed/want to run.
3) Click Run

Does that help?