Macro to copy and paste values for every cell containing a specific formula (i.e sumif, round or vlookup)

[phillipsdp]

Hi -

I currently have a working macro but need help to make it loop through every sheet rather than only working on the active sheet. below is my current macro if you could help that would be GREAT!


Sub CPV()

Set Rng = Cells.Find(What:="round", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Rng Is Nothing Then
Do
Rng.Formula = Rng.Value
Set Rng = Cells.FindNext(Rng)
Loop Until Rng Is Nothing
End If
End Sub

 

[CalcSux78]

Normally, to loop through all sheets in a workbook you would use:

Dim sh As Worksheet
For Each sh In Worksheets
    'do something here
    msgbox sh.Name
Next sh

 

[phillipsdp]

Yes I'm familiar with the common sheet loop. I just haven't been able to figure out how to use it in conjunction with this formula.

 

[phillipsdp]

I've added to the original formula to be able to replace multiple specified formulas at once. But still haven't cracked how to get it to loop through the workbook.

Sub AB()
Dim ws As Worksheet
Dim WhatToFind As Variant
Dim iCtr As Long


WhatToFind = Array("round", "sumif", "sumifs", "vlookup", "sumproduct", "today")
For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
With rng

Set rng = Cells.Find(What:=WhatToFind(iCtr), _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
Do
rng.Formula = rng.Value
Set rng = Cells.FindNext(rng)
Loop Until rng Is Nothing

End If
End With
Next
End Sub

 

[CalcSux78]

With what you're looking to do, I used ws.Select. Here's what I did with a test workbook. I put random formulas and values across 5 sheets in the workbook and was able to convert only the cells that contained a formula from the "WhatToFind" array.

Sub AB()
Dim ws As Worksheet
Dim WhatToFind As Variant
Dim iCtr As Long

WhatToFind = Array("round", "sumif", "sumifs", "vlookup", "sumproduct", "today")
For iCtr = LBound(WhatToFind) To UBound(WhatToFind)
    [COLOR=#ff0000]For Each ws In Worksheets
        ws.Select[/COLOR]
        Set Rng = Cells.Find(What:=WhatToFind(iCtr), _
            After:=[COLOR=#ff0000][A1][/COLOR], LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
        If Not Rng Is Nothing Then
            Do
                Rng.Formula = Rng.Value
                Set Rng = Cells.FindNext(Rng)
            Loop Until Rng Is Nothing
        End If
    [COLOR=#ff0000]Next ws
Next iCtr[/COLOR]
End Sub

 

[phillipsdp]

Your code works, but not completely. I work with large databooks with tons of worksheets. I think the last thing to do is to put in some sort of error check that keeps it from getting stuck when nothing is found within the first 300 rows to help speed it up and keep it from hanging on a worksheet that potentially has a large range due to a user mistake.

 

[phillipsdp]

ws.Select
I'm getting an error here. Can someone help handle errors caused by the code?