Prefix number with '

**JohnD0e** · 03-10-2010, 12:41 AM

I posted a question a few weeks ago and a solution was found and some great code was written by JBeaucaire and others. I now just need to enhance the maco a little and have the result prefixed with a '


Sub RoundSelection()
'Select a range and then run the macro
Dim Cell As Range, RNG As Range
Set RNG = Selection.SpecialCells(xlCellTypeConstants)

    For Each Cell In RNG
        If IsNumeric(Cell.Value) Then Cell.Value = Evaluate("ROUND(" & Cell & ",2)")
    Next Cell

End Sub

The macro works just fine and rounds a number to 2 decimal places but I need the resulting numberto be prefixed with a '

Eg at the moment

100.5346666 would become 100.53 and what i want to do is have it '100.53

I had tried the following

 If IsNumeric(Cell.Value) Then Cell.Value = Evaluate(""'"("ROUND(" & Cell & ",2)")")

But with out any luck

So Any help on this issue would be great ;-)

**shg** · 03-10-2010, 12:49 AM

Sub RoundSelection()
    Dim r As Range
    Dim cell As Range
    
    On Error Resume Next
    Set r = Selection.SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    
    If r Is Nothing Then Exit Sub

    For Each cell In r
        If IsNumeric(cell.Value) Then cell.Value = "'" & Round(cell.Value, 2)
    Next cell
End Sub

**DonkeyOte** · 03-10-2010, 03:48 AM

And for the sake of demo'ing the Eval method:

Sub RoundSelection()
Dim rngArea As Range
For Each rngArea In Selection.SpecialCells(xlCellTypeConstants).Areas
    With rngArea
        .NumberFormat = "@"
        .Value = Evaluate("IF(ISNUMBER(--(" & .Address & ")),ROUND(" & .Address & ",2),REPT(" & .Address & ",1))")
    End With
Next rngArea
End Sub

if you want the apostrophe as opposed to just storing as text - remove the NumberFormat line and use ""'""&ROUND(...)