Rounding macro that doesn't change the cell contents (universal)

[zorro6204]

What I want is a Ctrl - o (say) marco, that takes an existing cell, whatever's in it, and adds "=round(" to the front and ",0)" to the back. If I try to record a macro, with or without relative references, when I call it again, it repeats the cell contents I was recording the macro in!

Example, if I record a macro in a cell that has +170+20 in it, I invoke record, then type:

F2 (turns on edit)
Home
=round(
Delete key (because stupid Excel replaces the + with an +)
End
,0)
Enter

And then back to stop macro recording. The result is "=round(170+20,0)". Good. But, if I then go to the next cell that has +200+100 in it, and call Ctrl-O, guess what I get back? Yup "=round(170+20,0)", the same contents as the previous cell. The cell contents of the cell I'm trying to round are wiped out.

That's bloody worthless of course. Any way around this? Believe it or not I'm just now moving from 123 to Excel. This was so easy in 123.

 

[lrobbo314]

Re: How to: Rounding macro that doesn't change the cell contents (universal)

Welcome to the Forum. Try this.

Sub RoundIt()
ActiveCell.Value = Round(Evaluate(ActiveCell.Value), 0)
End Sub

 

[lrobbo314]

Re: How to: Rounding macro that doesn't change the cell contents (universal)

Also, assuming that you want to do it all in one fell swoop as opposed to one by one, the code below will do it. It assumes that your data is in column A starting in A1.

Sub AAO()
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("IF({1},REPLACE(@,1,1,""=""))", "@", .Address))
    .Value = Evaluate(Replace("IF({1},ROUND(@,0))", "@", .Address))
End With
End Sub

 

[zorro6204]

Re: How to: Rounding macro that doesn't change the cell contents (universal)

Okay, I'll try it, thanks.

 

[FormR]

Re: How to: Rounding macro that doesn't change the cell contents (universal)

Hi, if you want to preserve the formulas, here is another option you can try:

Sub addroundfx()
Dim c As Range
For Each c In Selection
    If c.HasFormula Then c.Formula = Replace(c.Formula, "=", "=round(", 1, 1, vbTextCompare) & ",0)"
Next c
End Sub

Select the cells you want to change and run the macro.