Decimal Place in Macor

[richmond23]

I have borrowed this macro which works fine, but I need to specify that each value in the result has two decimal places. I don't know any VBA

Sub Combine()
Dim J As Integer

If Selection.Cells.Count > 1 Then
For J = 2 To Selection.Cells.Count
Selection.Cells(1).Value = _
Selection.Cells(1).Value & Chr(10) & _
Selection.Cells(J).Value
Selection.Cells(J).Clear
Next J
End If
End Sub

[Sisyphus]

Hi,
This code should do the job:

Code: Select all

Option Explicit

Public Sub Combine()

    Dim J As Integer
   
    With Selection
        If .Cells.Count > 1 Then
            With .Cells(1)
                .NumberFormat = "@"
                .HorizontalAlignment = xlRight
                .Value = FormattedValue(.Cells(1))
            End With
            For J = 2 To .Cells.Count
                .Cells(1).Value = .Cells(1).Value & Chr(10) _
                                    & FormattedValue(.Cells(J))
                .Cells(J).Clear
            Next J
        End If
    End With
End Sub

Private Function FormattedValue(ByRef Cell As Range) As String

    Const NumFormat As String = "0.00"
    FormattedValue = Format(Cell.Value, NumFormat)
End Function


You can change the Constant NumFormat to something else (like "#,##0.00" if you want a thousands separator), and you can change the HorizontalAlignment to either xlLeft or xlCenter, if you don't like the alignment I thought you wanted.

[richmond23]

Thanks Sisyphus,
Your code worked perfectly.