Replace a formula with its result

[Eddy2]

Hi
I am trying to replace a forumla with its results with the following code (in a table):

Sub formtoval()
With ThisWorkbook.ActiveSheet

.Range(Range("D30"), Range("D30").End(xlDown)).Value = .Value

End With
End Sub

However it gives me run-time error "438"

Any idea?

Eivind

 

[oldbrewer]

record a macro that selects a cell and edit copies it, and edit paste special values it - and look at the code

 

[RoryA]

A worksheet doesn't have a Value property. You need this:

Sub formtoval()
With ThisWorkbook.ActiveSheet

   With .Range(.Range("D30"), .Range("D30").End(xlDown))
      .Value = .Value
   End With

End With
End Sub

Note the full stops in front of all the Range calls.

 

[Eddy2]

Work like a charm! Cheers!