Adding ROUND to the beginning of multiple formulas

[hockey390]

Hi everyone,

Is it possible to add a formula prefix to multiple existing formulas? I have 500 unique formulas that I want to add ROUND to without manually typing it in.

Example:
Existing formulas:
A1 =B1+B2
A2 = B75-B73
A3 = R1-R2+R7

Want:
A1 = ROUND(B1+B2,1)
A2 = ROUND(B75-B73,1)
A3 = ROUND(R1-R2+R7,1)

Thanks!

 

[MrKowz]

Are all of the formulas in a specific, contiguous, range?

 

[hockey390]

All formulas are in cells A3:A718, but they are all different and pull data from multiple sources. No one formula is the same so I can't just add to the first and copy over.

I can easily add =ROUND(A1) into cell B1, but that creates a second formula, I can only have one.

One thought was creating a VBA code to pull the formula in the cell into the cell next to it as text, then creating a code to cocatenate the text and munipulate into a formula.. Doesn't seem simple by any means.

 

[MrKowz]

Try the following code:

Public Sub RoundFormulas()
Dim i   As Long, _
    LR  As Long, _
    tmp As String
    
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To LR
    tmp = Range("A" & i).Formula
    Range("A" & i).Formula = "=ROUND(" & Right$(tmp, Len(tmp) - 1) & ",1)"
Next i
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[hockey390]

That worked, thanks for the help.

 

[MrKowz]

That worked, thanks for the help.

Glad it works. Thank you for the feedback.

 

[hockey390]

Hi again,

This code stops when it hits a blank line. Is there a way to adjust so it stops after 10 blanks instead?

Thanks

 

[Scott Huish]

It should stop when it hits the last used row.
What are you going to put the =ROUND around if it is blank?