Paste non-adjacent formulas - values only

0
votes

Is there VBA code that will allow me to search for all cells containing formulas, copy them and paste over them values only?

I tried F5, Special, Formulas. At that point it will let me copy all the non-adjacent cells, but I receive the message that

this action won’t work on multiple selections

when I try to paste them values only.

EDIT: My original question was terrible...I'm specifically trying to search for BizNet formulas only. They would contain text such as YTDENDBAL, YTDENDBALBU, MTDEND, MTDNET, etc. Any other general Excel formulas should remain. When I need to save a document and email it to someone who doens't have BizNet, the formulas do not work. So I need to copy and paste over them values only.

Thanks again.

1
excelvba
Why not just paste values on the entire range? – BigBen
IF you want to do this will all formulas, just paste values into all cells of the sheet. @ScottCraner For me, your comment is a total valid answer to be posted – Foxfire And Burns And Burns
Thank you for the responses. I edited my original question. – Keira
In this case I would loop. – BigBen
I'm garbage at looping. Do you by chance have sample code? – Keira

1 Answers

1
votes

Something like this should do it:

    Dim MyTargetFunctions as Variant
    MyTargetFunctions = Array("YTDENDBAL", "MTDEND", "MTDNET")
    Dim MyCell As range
    For Each MyCell In ActiveSheet.UsedRange
        If MyCell.HasFormula Then
            Dim txt As Variant
            For Each txt In TargetFunctions
                If InStr(1, MyCell.Formula, txt) <> 0 Then
                    MyCell.Formula = MyCell.value
                    Exit For
                End If
            Next txt
        End If
    Next MyCell