VBA Code Assistance: Formula to Value

[jstevens]

Is it possible to change every cell within a sheet whose formula contains a certain word to a value? In my case the formula is referencing another sheet.

I was thinking of using activesheet.cells. After giving it consideration, the code would be looking at every cell and would not be very efficient.

CurrentRegion would not work because not all cells are contiguous.

Regards,
John

[mbarron]

You can use 'special cells' to limit your search to only cells containing formulas.

Code: Select all

Sub ChangeToValues()
Dim rForms As Range, rChange As Range
Set rForms = Cells.SpecialCells(xlCellTypeFormulas, 23)
For Each rChange In rForms
    If InStr(1, rChange.Formula, "Sheet2") Then
        rChange.Value = rChange.Value
    End If
Next
End Sub

[jstevens]

mbarron,

It works wonderfully.

Thank you,
John