Excel VBA - Check cells for specific PIECE of formula

[jdorby]

Hi!

I was hoping someone can help me with some VBA to loop through all the cells, or an active selection, and if it finds a cell with a formula that starts with "=VLOOKUP" or "GETPIVOTDATA", then copy and paste values for those cells. If it comes across a cell that starts with "=SUM" then it leaves it as is. I've been looking for way to insert a wildcard to make this work, but can't seem to find anything. Thank you so much in advance!
-Jim

 

[Domenic]

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=darkblue]Dim[/COLOR] rFormulas       [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell           [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] Cnt             [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]

    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rFormulas = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    Cnt = 0
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rFormulas [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rFormulas
            [COLOR=darkblue]With[/COLOR] rCell
                [COLOR=darkblue]If[/COLOR] Left(.Formula, 8) = "=VLOOKUP" [COLOR=darkblue]Or[/COLOR] Left(.Formula, 13) = "=GETPIVOTDATA" [COLOR=darkblue]Then[/COLOR]
                    Cnt = Cnt + 1
                    .Value = .Value
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] rCell
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
    MsgBox "Number of cells converted to values:  " & Cnt
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[jdorby]

Couldn't have worked any better. You just helped me so much! You're amazing! Thank you!! - Jim