Removing =ROUND functions from workbook

[theanchorman]

Hello, I have a Excel file that has previously had ROUND formulas in many locations of the workbook. Almost 11k cells with ROUND formulas. Is there a VBA code I can use to remove all or most of them? I need to remove the beggining part of the formula "=ROUND(" and the end of it ",0)". Any suggestions?

 

[JoeMo]

Hello, I have a Excel file that has previously had ROUND formulas in many locations of the workbook. Almost 11k cells with ROUND formulas. Is there a VBA code I can use to remove all or most of them? I need to remove the beggining part of the formula "=ROUND(" and the end of it ",0)". Any suggestions?

Post the formula or formulas you want to replace and for each the replacement you want to make.

 

[rollis13]

Hi to all.
This macro would probably do the job for you. It will run on all cells of the used range in the active sheet.

Option Explicit
Sub test()
    Dim cel    As Range
    For Each cel In ActiveSheet.UsedRange
        If cel.HasFormula = True And Left(cel.Formula, 7) = "=ROUND(" Then cel.Formula = "=" & Mid(cel.Formula, 8, Len(cel.Formula) - 10)
    Next cel
End Sub

No testing for merged cells or locked cells or whatever.

 

[theanchorman]

Post the formula or formulas you want to replace and for each the replacement you want to make.

This file has various ROUND formulas at listed below. Essentially, I just want to remove the ROUND from the entire formula at let the formula calculate as is.

=ROUND(A200/D200* F150,0)
=ROUND(A200*A150,0)
=ROUND((+A200+D200)*F150,0)
=ROUND((+A200+B200+D200)*F150,0)
=ROUND(SUM(A200:AD200)*F150+F180,0)

 

[theanchorman]

Hi to all.
This macro would probably do the job for you. It will run on all cells of the used range in the active sheet.

Option Explicit
Sub test()
    Dim cel    As Range
    For Each cel In ActiveSheet.UsedRange
        If cel.HasFormula = True And Left(cel.Formula, 7) = "=ROUND(" Then cel.Formula = "=" & Mid(cel.Formula, 8, Len(cel.Formula) - 10)
    Next cel
End Sub

No testing for merged cells or locked cells or whatever.

Thanks @rollis13! This helped a lot as I was able to get rid of 90% of the formulas, but the macro would give me an error at times as maybe some of the formulas are a bit different with ROUND functions in it. Any other suggesstions?

 

[rollis13]

Oh, that would need the use of function Find to search for a Round formula inside a bigger formula. Sorry, but this goes a little beyond my knowledge .
That's probably why @JoeMo asked about your used formulas but you didn't say anything about Round embedded in formulas .

 

[johnnyL]

You may want to create a script to print out out all of the formulas & Locations of each formula to allow you to see what the structure of each formula is.