Changing cell formulas with a macro or VBA
 

Have a co-worker that wants to change the formulas in his spreadsheet
to add "=round(" to the front and ",0" to the end. Formulas may be
long or short and there are plenty of them.

He would like a simple macro so he could activate a cell, and hit the
shortcut keys, and make the conversion.

Thanks for any help.

Changing cell formulas with a macro or VBA
 

See this message I just posted (Subject: @ Round Formulas)



In article ,
(JDeBeer) wrote:

Have a co-worker that wants to change the formulas in his spreadsheet
to add "=round(" to the front and ",0" to the end. Formulas may be
long or short and there are plenty of them.

He would like a simple macro so he could activate a cell, and hit the
shortcut keys, and make the conversion.

Thanks for any help.

Changing cell formulas with a macro or VBA
 

JE McGimpsey just posted this in response to a similar post:

One way:

Select the range of cells you want to convert. The range can include
Text, constants, etc - only the formulae will be converted. Change
the "0" in the cell.Formula line to the number of places you want to
round to.

Public Sub WrapARound()
Dim cell As Range
On Error Resume Next
For Each cell In Selection.SpecialCells(xlCellTypeFormulas)
cell.Formula = "=ROUND(" & Mid(cell.Formula, 2) & ",0)"
Next cell
On Error GoTo 0
End Sub


You might add: xlNumbers in the Special Cells and also, if he is only
going to do one cell, then I added some code for that as well

Public Sub Wrap_A_Round()
Dim cell As Range, rng as Range
On Error Resume Next
if Selection.Count = 1 then
if selection.HasFormula then
set rng = cell
else
set rng = Nothing
end if
else
set rng = Selection.SpecialCells(xlCellTypeFormulas,xlNumber s)
End if
if not rng is nothing then
For Each cell In rng
cell.Formula = "=ROUND(" & Mid(cell.Formula, 2) & ",0)"
Next cell
End if
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy

"JDeBeer" wrote in message
om...
Have a co-worker that wants to change the formulas in his spreadsheet
to add "=round(" to the front and ",0" to the end. Formulas may be
long or short and there are plenty of them.

He would like a simple macro so he could activate a cell, and hit the
shortcut keys, and make the conversion.

Thanks for any help.

Changing cell formulas with a macro or VBA
 

If ActiveCell.Formula < "" Then
ActiveCell.Formula = "=ROUND(" _
& Right(ActiveCell.Formula, Len(ActiveCell.Formula) _
- 1) & ",0)"
End If

If I undestood right:

1. check cell has formula
2. attach =round( and ,0) to the cell's existing formula
minus original '=' (always 1st) char.


-----Original Message-----
Have a co-worker that wants to change the formulas in his
spreadsheet
to add "=round(" to the front and ",0" to the end.
Formulas may be
long or short and there are plenty of them.

He would like a simple macro so he could activate a cell,
and hit the
shortcut keys, and make the conversion.

Thanks for any help.
.

Changing cell formulas with a macro or VBA
 

or to use David McRitchie's technique (makes the code more compact and fewer
instructions)

Public Sub Wrap_A_Round()
Dim cell As Range, rng as Range
On Error Resume Next
set rng =
Intersect(Selection.Selection.SpecialCells(xlCellT ypeFormulas,xlNumbers))
if not rng is nothing then
For Each cell In rng
cell.Formula = "=ROUND(" & Mid(cell.Formula, 2) & ",0)"
Next cell
End if
On Error GoTo 0
End Sub


--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
JE McGimpsey just posted this in response to a similar post:

One way:

Select the range of cells you want to convert. The range can include
Text, constants, etc - only the formulae will be converted. Change
the "0" in the cell.Formula line to the number of places you want to
round to.

Public Sub WrapARound()
Dim cell As Range
On Error Resume Next
For Each cell In Selection.SpecialCells(xlCellTypeFormulas)
cell.Formula = "=ROUND(" & Mid(cell.Formula, 2) & ",0)"
Next cell
On Error GoTo 0
End Sub


You might add: xlNumbers in the Special Cells and also, if he is only
going to do one cell, then I added some code for that as well

Public Sub Wrap_A_Round()
Dim cell As Range, rng as Range
On Error Resume Next
if Selection.Count = 1 then
if selection.HasFormula then
set rng = cell
else
set rng = Nothing
end if
else
set rng =
Selection.SpecialCells(xlCellTypeFormulas,xlNumber s)
End if
if not rng is nothing then
For Each cell In rng
cell.Formula = "=ROUND(" & Mid(cell.Formula, 2) & ",0)"
Next cell
End if
On Error GoTo 0
End Sub

--
Regards,
Tom Ogilvy

"JDeBeer" wrote in message
om...
Have a co-worker that wants to change the formulas in his spreadsheet
to add "=round(" to the front and ",0" to the end. Formulas may be
long or short and there are plenty of them.

He would like a simple macro so he could activate a cell, and hit the
shortcut keys, and make the conversion.

Thanks for any help.

Changing cell formulas with a macro or VBA
 

This worked in Excel97

Sub myformula()
' Keyboard Shortcut: Ctrl+q

ActiveCell.Formula = "=round(" & Right(ActiveCell.Formula,
Len(ActiveCell.Formula) - 1) & ",0)"
End Sub

watch word wrap
change 'q' to your choice.

--
sb
"JDeBeer" wrote in message
om...
Have a co-worker that wants to change the formulas in his spreadsheet
to add "=round(" to the front and ",0" to the end. Formulas may be
long or short and there are plenty of them.

He would like a simple macro so he could activate a cell, and hit the
shortcut keys, and make the conversion.

Thanks for any help.

Changing cell formulas with a macro or VBA
 

Thanks for everyone's help.

jdb