Help: need a macro to paste values conditional on formula being sumproduct

[msantas]

I have a workheet which I use "sumproduct" formulas to pull in new data every week. Within the worksheet there are also "sum" formulas that gives subtotals in certain sections. I want to create a macro that pastes values over the sumproduct formulas without pasting values over the sum formulas. However I don't want to have to specify a specific range within the worksheet to paste values because these ranges will change.

So basically I need a script that can paste values conditional on the formula being a sumproduct. Is this possible? Thanks!

 

[msantas]

I found a script that does almost exactly what I need. However this script pastes values over all Sumproduct formulas in the entire worksheet. I need it to specify a particular column. How can I make that adjustment?

Sub RemoveSumproductFormulas()
Dim rng As Range
With Worksheets("Centers")
For Each rng In .UsedRange
If rng.Formula Like "*SUMPRODUCT*" Then rng.Formula = rng.Value
Next rng
End With
End Sub

 

[msantas]

More specifically I need to specify it only paste values in the column of the current active cell.

I found a script that does almost exactly what I need. However this script pastes values over all Sumproduct formulas in the entire worksheet. I need it to specify a particular column. How can I make that adjustment?

Sub RemoveSumproductFormulas()
Dim rng As Range
With Worksheets("Centers")
For Each rng In .UsedRange
If rng.Formula Like "*SUMPRODUCT*" Then rng.Formula = rng.Value
Next rng
End With
End Sub