Modify a part of formula in a lot of cells

vladimiratanasiu · Apr 14, 2018

Hello!
I attached an excel file with more tables, all of them including a lot of cells that contain the ROUND function. Giving some different values resulted from calculations, I need to modify all cells with this function. As fully as possible Id like to change the structure of syntax, by removing completely from the whole formula just the part with the ROUND function, and keeping available the rest of it. For example, it sounds to change the commands
= ROUND(I20/$D$4,3) ----> =I20/$D$4;

=ROUND(N25/$D$4,3) ----> =N25/$D$4

etc.
Under these circumstances, Is there any solution to automatically do this operation in all referred cells ?
Sincerely yours,
Vladimir A.
https://www.dropbox.com/s/13hco13qv95eort/Book4.xlsx?dl=0

yky · Apr 15, 2018

You can do it manually or write a script to do it.

To do it manually, first, go to Formulas/Show Formulas. So, you can see what is happening.

1. select all cells whose formula you want to change. This can be non-contiguous.
2. Do a Find/Replace to remove all equal signs (replace equal sign with null). In essence, delete them.
3. Replace all "Round" with null. Delete them.
4. Replace all ",3)" with null. Delete them.
5. Replace all "(" sign with "=". Done.

vladimiratanasiu · Apr 15, 2018

Thank you for your response! Unfortunately, I had tried this solution (including also firstly the option to select and remove the phrase"=ROUND") and it didn't run, generating at the end the error from one attached image. On the other hand, as it's seen in the other printscreen the spreadsheet is much larger, including other connected tables to be affected when changes are done. So, is there any other solution available for this issue?

https://www.dropbox.com/s/oebjqnbbfomsxel/Untitled.png?dl=0
https://www.dropbox.com/s/suw7t8y4gdz7cl2/Untitled1.png?dl=0

Peter_SSs · Apr 15, 2018

vladimiratanasiu said:
Unfortunately, I had tried this solution (including also firstly the option to select and remove the phrase"=ROUND") and it didn't run, generating at the end the error from one attached image.

The method suggested worked for me on the sample file that you gave in post #1 , but you do have to be careful with step 1 "select all cells whose formula you want to change.". I suspect it didn't work for you because you also selected some cells that had formulas without the =ROUND( function at the start (eg columns G, L N, Q)

However, if you have a lot of disjoint ranges, & to avoid changing non-relevant formulas accidentally, you could try this macro in a copy of your workbook.

If it needs to run on multiple sheets in the workbook, post back with details of which sheets it has to run on and/or which sheets it should not run on.

Code:

Sub ReplaceRound()
  Dim rFormulas As Range, c As Range
  Dim s As String
  
  On Error Resume Next
  Set rFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlNumbers)
  On Error GoTo 0
  If Not rFormulas Is Nothing Then
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each c In rFormulas
      s = c.Formula
      If Left(s, 7) = "=ROUND(" Then c.Formula = "=" & Mid(Left(s, InStrRev(s, ",") - 1), 8)
    Next c
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
  End If
End Sub

vladimiratanasiu · Apr 15, 2018

Hello!
I think it’s better to present in a large manner the issue that determined me to ask you the previous clarifications. I designed a feasibility study simulation (attached to my message), including all calculations specific for a production unit. Throughout this document, they are three relevant spreadsheets I refer to:
1. DetaliereDG, including the detailed estimate for all costs of investment, divided into specific budget chapters.
2. DG / Centralizator devize, presenting the summary situation of the budget chapters detailed in the previous file.
3. Buget indicativ, containing the project indicative budget, divided into chapters and eligible/ non-eligible costs.
As it’s seen into the files DetaliereDG and DG / Centralizator devize , the final sum of the investment is bolded into red color and accounts a value of 650.705 lei (RON) / 146.487 Euro (including VAT). The problem appears in the file Buget indicativ, where the final sum in Euro keeps the same value, but the value in lei/RON decreases from 650,705 to 650,695 RON. I traced the references of the calculations to find out any previous error / omission, and still can’t solve the trouble. The only assumption is that the value expressed in RON / lei met small variations during the conversion from Euro to RON and relating to VAT, and so increasing the final sum because of these changes. And I supposed these variations are determined also by the ROUND function, which aggregates and rounds them in a final greater value.
This is the basic reason that determined me to ask you a solution, and it’s still available taking into account the larger exposed context. I attached to you the draft of mentioned simulation, and hope it makes more clear my issue for the right solution.
Vladimir A.


https://www.dropbox.com/s/lpsjvd7v6l9gohu/07.0 Prognoze SF_porci-FNC.xlsm?dl=0

yky · Apr 15, 2018

vladimiratanasiu said:
Thank you for your response! Unfortunately, I had tried this solution (including also firstly the option to select and remove the phrase"=ROUND") and it didn't run, generating at the end the error from one attached image.

I see that in the draft file, two ROUNDDOWN function are used. Then, the method I suggested would not work because once "ROUND" is deleted, "ROUNDDOWN" becomes "DOWN". Furthermore, there are 59 ROUND functions that end with ",0)", not ",3). They will not work either.

Anyway, after replacing formulas, I got 650.706, not 650.705. Is that what you want?

Here is how I did it. You want to do the replacement on the entire workbook, not just on worksheet.

1. replace all equal signs with "xxxccc" or whatever string that doesn't appear in the workbook.
2. manually delete the two ROUNDDWON functions.
3. remove all "ROUND(".
4. remove all ",3)".
5. remove all ",0)".
6. replace "xxxccc" with "=".
7. manually enter the two ROUNDDOWN functions back.

Peter_SSs · Apr 16, 2018

So, did you try the code from post #4 ?

a) If so, did it produce incorrect results?
- If so, could you give 2 or 3 sheet and cell references for the sample file linked to in post #5 where the code gave the wrong results and what the correct result (& formula) should have been for those cells?

b) If you didn't try it, would it be worth giving it a try?

yky · Apr 16, 2018

It worked with little modification. Need to run the code through all sheets, not just the ActiveSheet.

Peter_SSs · Apr 16, 2018

yky said:
It worked with little modification. Need to run the code through all sheets, not just the ActiveSheet.

Not sure who you are referring to. If it is me, yes, I already mentioned that to the OP

Peter_SSs said:
If it needs to run on multiple sheets in the workbook, post back with details of which sheets it has to run on and/or which sheets it should not run on.

I certainly haven't noticed anywhere that it specifically states to run on all worksheets. Post #5 mentions only 4 out of the 13 worksheets in the workbook but I would like exact confirmation that
- those are the only 4 sheets to act on**, and
- on those sheets every =ROUND(... formula should be changed.

** Even then I'm not sure those 4 sheets all need checking. For example, I was not able to find any =ROUND(... formulas on the 'Centralizator devize' sheet, I couldn't find any such formulas. I know that wouldn't stop my code from running, or cause any problems, but I would like to know so that the code is efficient and no unintended consequences might crop up.

yky · Apr 16, 2018

Peter_SSs said:
I certainly haven't noticed anywhere that it specifically states to run on all worksheets. Post #5 mentions only 4 out of the 13 worksheets in the workbook but I would like exact confirmation that
- those are the only 4 sheets to act on**, and
- on those sheets every =ROUND(... formula should be changed.

I wasn't as careful as you are. I did not check the formulae in every sheet to see if they needed modification. I just modified your code so it ran through all sheets. At the end, I got 650,706. I did not see any error such as #Value . So, I concluded the code worked.

Modify a part of formula in a lot of cells

vladimiratanasiu

Active Member

Excel Facts

yky

Well-known Member

vladimiratanasiu

Active Member

Peter_SSs

MrExcel MVP, Moderator

vladimiratanasiu

Active Member

yky

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

yky

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

yky

Well-known Member

Similar threads

Forum statistics

Share this page

Modify a part of formula in a lot of cells

Active Member

Excel Facts

Well-known Member

Active Member

MrExcel MVP, Moderator

Active Member

Well-known Member

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock