How to align numbers right and text left in 1 cell with macro

[lehota]

Hello,

I would like to know if it is possible to apply 2 different alignments within 1 cell with a macro - I would like to do something like this (see below) but do not know how to do it to align $Ms below each other exactly.

Opening balance 1.0 $M
Movements w/o EAFE 4.0 $M

My code looks like this:

ce = Sheets("Fluctuation analysis for CF100").Cells(c, 3) & WorksheetFunction.Rept(" ", 100 - Len(Sheets("Fluctuation analysis for CF100").Cells(c, 3))) & WorksheetFunction.Text(WorksheetFunction.Round(Sheets("Fluctuation analysis for CF100").Cells(c, 4), 1), "#,##0.0") & " $M " & Chr(10)

I want data in bold to be right aligned to the right border of the cell while everything else should be left aligned to the left border of the cell

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Here's one approach that uses the Distributed HorizontalAlignment ...

Sub Align_Left_and_Right()
    Dim sLeftText As String, sRightText As String
    Dim c As Long
    c = 2

    With Sheets("Fluctuation analysis for CF100")
        sLeftText = Replace(.Cells(c, 3), " ", Chr(160))
        sRightText = Replace(Format(Round(.Cells(c, 4), 1), "#,##0.0") _
            & " $M ", " ", Chr(160))

         With .Cells(c, 5)
            .HorizontalAlignment = xlDistributed
            .Value = sLeftText & " " & sRightText
        End With
    End With
End Sub

Chr(160) is a non-breaking space character.

 

[lehota]

Just perfect - thank you so much