Find All cells in workbook containing specific formula, convert to values.

[LenPL]

All,

I am trying to find a method for converting all cells in entire workbook starting with the formula "=cc." to values.

This is what I have so far.

Sub
    Do Until IsNotFound
    
    Cells.Find(What:="=cc.", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
    
    Selection.Copy
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Loop
    
        
End Sub

There are two issues at present, it only works on the current worksheet, and secondly it returns a Run-time '91' Error on completion of the sheet (altough I can live with this issue).

Thank you in advance.

Len

 

[hippiehacker]

give this a try

Sub SearchFormula()
Dim mySearch()
Dim i As Integer
Dim c As Range
Dim ws As Worksheet
mySearch = Array("cc.")
For i = 0 To UBound(mySearch)
 For Each ws In Worksheets
 On Error Resume Next
 With ws.UsedRange.SpecialCells(xlCellTypeFormulas)
     Set c = .Find(mySearch(i), LookIn:=xlFormulas)
     Do
         c.Value = c.Value
         Set c = .FindNext(c)
     Loop Until c Is Nothing
 End With
 Next ws
Next i
    
End Sub

 

[Wessie]

Hi Len

Firstly, the issue with it only running in one worksheet you can do the following:

Before the do enter the following:


"Dim a as integer

For a = 1 to Sheets.Count
Sheets(a).Select
With Worksheet do
Range("A1").Select"

And then after the loop
"End with
Next"

Also try and define your range to run through. Otherwise your coding might be heavy.

Hope this helps

 

[LenPL]

give this a try

Sub SearchFormula()
Dim mySearch()
Dim i As Integer
Dim c As Range
Dim ws As Worksheet
mySearch = Array("cc.")
For i = 0 To UBound(mySearch)
 For Each ws In Worksheets
 On Error Resume Next
 With ws.UsedRange.SpecialCells(xlCellTypeFormulas)
     Set c = .Find(mySearch(i), LookIn:=xlFormulas)
     Do
         c.Value = c.Value
         Set c = .FindNext(c)
     Loop Until c Is Nothing
 End With
 Next ws
Next i
    
End Sub

HippeHacker,

I have no idea what you have done, but it seems to work like a dream (could be faster)! Thank you very much.

Len