Remove Round in batch
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Remove Round in batch
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?
-
- Administrator
- Posts: 78484
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Remove Round in batch
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
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Remove Round in batch
Thanks very much....
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Remove Round in batch
And when I have only =ROUND(H4,0) how can i change the code to work?
-
- Administrator
- Posts: 78484
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Remove Round in batch
Change
to
Code: Select all
Set rng = .Find(What:="=ROUND(*/1000,0)", LookIn:=xlFormulas, LookAt:=xlWhole)
Code: Select all
Set rng = .Find(What:="=ROUND(*,0)", LookIn:=xlFormulas, LookAt:=xlWhole)
Best wishes,
Hans
Hans