Macro to modify the fomular in highlighted Excel cells?

[jtb1492]

I need to write a macro that will modify the fomula in the highlighted cells.

Cell B2: =4.325112
Cell C2: =A1*A2

I'd like to highlight cells B2 and C2 run the macro, the macro asks me to input an integer, and then the forumals in the highlighted range are modified as follows:

Cell B2: =rounddown(4.325112, Number_I_Entered)
Cell C2: =rounddowm(A1*A2, Number_I_Entered)

Any help would be appreciated.

 

[SkipVought]

Hi,

Turn on your macro recorder and enter the formulas as desired.

Observe the code.

Post back with your code if you still need help.

Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[jtb1492]

Thanks, Skip. But I don't even know how to do it so that the macro recorder will realize it only modifying the formula that is already in the cell and not just entering what I change it do. The is what the recorder gives me, which is pretty much useless:

Sub Macro1()
ActiveCell.FormulaR1C1 = "=ROUNDDOWN(3.12545,2)"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN(R[-3]C*R[-2]C,3)"
Range("E9").Select
End Sub

 

[jtb1492]

In plain english, I want to take the fomula in a call and modify it by adding text the to beginning and end, for every cell I have highlighted.

 

[SkipVought]

What does your last statement mean? Anything that you can do in a cell you can do in VB code. Where is the text that you want to add to the beginning and end? You did not include that in yor code.


Skip,
[sub]
[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen![/sub]

 

[jtb1492]

I want to add the text "rounddown(" to the beginning and the text ",3)" to the end of the current formula in the cell (no quotes obviosly), for every cell I have highlighted, no matter what the current formula in the cell is.

 

[byundt]

jtb1492,
Here is some code that generalizes your recorded macro. Install it in a regular module sheet (same place as your recorded macro was stored), then run it with ALT + F8 to open the macro selector. The macro works on selected cells, and it will change cells containing formulas (adding ROUNDDOWN and a user-specified number of digits), but not those containing constants.

Sub RoundingDown()
Dim cel As Range
Dim i As Integer
Dim x As Variant
x = Application.InputBox("How many digits do you want to round down to?")
If x = False Then Exit Sub

i = x
Application.ScreenUpdating = False
For Each cel In Selection.Cells
    If cel.HasFormula Then cel.Formula = "=ROUNDDOWN(" & Mid$(cel.Formula, 2) & "," & i & ")"
Next
Application.ScreenUpdating = True
End Sub

Brad

 

[jtb1492]

Wow, Brad, looks great. Why did you neglect constants? Would it be hard to have this work on them too, because I would like it to be able to. Could we add something like:

else cel.Formula = "=ROUNDDOWN(" & IDunnoWhatToPutHere & "," & i & ")

 

[byundt]

jtb1492,
I assumed that you would have already input constants with the desired precision. But if you want to round them down also, then try this tweak to the original code. It puts a rounded constant back in the same cell. It also ignores blanks, text and error values.

Sub RoundingDown()
Dim cel As Range
Dim i As Integer
Dim x As Variant
x = Application.InputBox("How many digits do you want to round down to?")
If x = False Then Exit Sub

i = x
Application.ScreenUpdating = False
On Error Resume Next
For Each cel In Selection.Cells
    If cel.HasFormula Then
        cel.Formula = "=ROUNDDOWN(" & Mid$(cel.Formula, 2) & "," & i & ")"
    ElseIf cel <> "" Then
        x = Application.RoundDown(cel, i)
        If Not IsError(x) Then cel = x
    End If
Next
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

Brad

 

[byundt]

If you prefer to keep the full value of the constants in a ROUNDDOWN formula, then here is yet another version of the code:

Sub RoundingDown()
Dim cel As Range
Dim i As Integer
Dim X As Variant
X = Application.InputBox("How many digits do you want to round down to?")
If X = False Then Exit Sub

i = X
Application.ScreenUpdating = False
For Each cel In Selection.Cells
    If cel.HasFormula Then
        cel.Formula = "=ROUNDDOWN(" & Mid$(cel.Formula, 2) & "," & i & ")"
    ElseIf cel <> "" Then
        X = Application.RoundDown(cel, i)
        If Not IsError(X) Then cel.Formula = "=ROUNDDOWN(" & cel.Formula & "," & i & ")"
    End If
Next
Application.ScreenUpdating = True
End Sub

Brad