Convert Specific formula to value :


Many times i have seen this query on Excel discussion groups and forums to convert values for a specific formulas on worksheets. so i have decided to post a something about that. it might be little helpful for Excel users.

here is a code which will convert formulas to values on every sheets, this code asks formula name which need to be converted in value and then start looking the formulas  and where any cells contain that specific formulas (not only that single formula but it can be with in another formula) it will evaluate that formula and will put the value in the cells.

Sub ConvertFormulaToValues()
    Dim rngRange As Range
    Dim VarArr
    Dim strFormula As String
    Dim varEle
    Dim lngR As Long
    Dim lngC As Long
    Dim lngCount As Long
    Dim wksSheet As Worksheet
    Dim wksLastActive As Worksheet
    strFormula = InputBox(“Enter formula name to replace with values”)
    lngCount = 0
    Application.ScreenUpdating = False
    Set wksLastActive = ThisWorkbook.ActiveSheet
    For Each wksSheet In ThisWorkbook.Worksheets
        Set rngRange = wksSheet.UsedRange
        If Not rngRange Is Nothing Then
            VarArr = rngRange.Formula
                If IsArray(VarArr) Then
                    wksSheet.Activate
                    For lngR = LBound(VarArr) To UBound(VarArr)
                        For lngC = LBound(VarArr) To UBound(VarArr, 2)
                            If Not IsEmpty(VarArr(lngR, lngC)) Then
                                If UCase(VarArr(lngR, lngC)) Like “*” & UCase(strFormula) & “*” Then
                                        If wksSheet.Cells(lngR, lngC).HasFormula Then
                                            VarArr(lngR, lngC) = Evaluate(VarArr(lngR, lngC))
                                            lngCount = lngCount + 1
                                            Else
                                            VarArr(lngR, lngC) = VarArr(lngR, lngC)
                                        End If
                                End If
                            End If
                        Next lngC
                    Next lngR
                    wksSheet.UsedRange.Value = VarArr
                End If
                Set rngRange = Nothing
        End If
    Next wksSheet
    wksLastActive.Activate
    Application.ScreenUpdating = True
    MsgBox strFormula & ” has been replaced in ” & lngCount & ” Cells”, vbInformation
End Sub
Most of time people have different requirements, e.g. they want to convert formulas to values on a single worksheet or in a selected range. but this code is not so dynamic, but this will give an idea on “how to” complete this task as faster as it can.

One Comment Add yours

  1. Jomili says:

    I have a workbook with multiple VLookups, wrapped in “IFERROR”. In the prompt, I put “VLookup”. Got the message back “VLookup has been replaced in 0 cells”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s