CODEWITHSUNDEEP
vbaexcel
user3730604
user3730604

Reputation: 25

vba convert formula into value if value is >0 in specific range

A question: I am trying to convert a formula in a specific range into its value once the value of that formula is more than 0 in vba. I have found some results for converting an entire workbook to just it's value, but nothing more detailed than that.

Sub ConvertCellsToValues()
         ActiveSheet.Cells.Copy
         ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
End Sub

Upvotes: 0

Views: 4025

Answers (2)

dcromley
dcromley

Reputation: 1410

The .Copy and .PasteSpecial work for any range:

Sub ConvertCellsToValues(Param as Range)
  Param.Copy
  Param.PasteSpecial Paste:=xlPasteValues
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96773

Say the range in question is A1 thru A10.

Place the following event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
    Dim A As Range, r As Range
    Set A = Range("A1:A10")
    For Each r In A
        If r.HasFormula And r.Value > 0 Then
            r.Value = r.Value
        End If
    Next r
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

  1. right-click the tab name near the bottom of the Excel window
  2. select View Code - this brings up a VBE window
  3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE windows as above
  2. clear the code out
  3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Upvotes: 1

Related Questions