Remove Round in batch

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Remove Round in batch

Post by vaxo »

Hello, I have =ROUND('K-Lead PPE Register'!B165/1000,0) this function all around how can I remove this round and /1000 in batch with find replace or with formula?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Round in batch

Post by HansV »

Run the following macro:

Code: Select all

Sub RemoveRound()
    Dim rng As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet.UsedRange
        Set rng = .Find(What:="=ROUND(*/1000,0)", LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not rng Is Nothing Then
            Do
                rng.Formula = "=" & Mid(rng.Formula, 8, Len(rng.Formula) - 15)
                Set rng = .FindNext(After:=rng)
            Loop Until rng Is Nothing
        End If
    End With
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Remove Round in batch

Post by vaxo »

Thanks very much....

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Remove Round in batch

Post by vaxo »

And when I have only =ROUND(H4,0) how can i change the code to work?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove Round in batch

Post by HansV »

Change

Code: Select all

        Set rng = .Find(What:="=ROUND(*/1000,0)", LookIn:=xlFormulas, LookAt:=xlWhole)
to

Code: Select all

        Set rng = .Find(What:="=ROUND(*,0)", LookIn:=xlFormulas, LookAt:=xlWhole)
Best wishes,
Hans