formatting question

[ricksimm]

I use the following code to build a two column array with dollar amounts in the first column and part description in the second column and then display the list.

                        i = 1
                        With Sheets("Main").Range("LBstart")
                                For Each c In Sheets("Main").Range(clientPPT, Sheets("Main").Range(clientPPT).End(xlDown))
                                        .Cells(i, 1) = c.Offset(0, 1).Value
                                        .Cells(i, 2) = c.Value
                                        i = i + 1
                                Next c
                        End With
                        With DetailsListBox
                                .ColumnCount = 2
                                .ColumnWidths = "44pt;200pt"
                                .List = Sheets("Main").Range("LBstart", Sheets("Main").Range("LBstart"). _
                                        Offset(65536 - Range("LBstart").Row, 1).End(xlUp)).Value
                                For i = 0 To .ListCount - 1
                                        .List(i) = Format(.List(i), "#,###.00")
                                Next
                       End With

When the list displays the dollar amounts are not right justified in the first list column. Can someone tell me how to make the dollar amounts right justify? Thanks.

 

[pgc01]

EDIT: Rethinking...

 

[Norie]

rick

What exactly do you mean the amounts aren't right justified?

 

[ricksimm]

column displays-
20.00
125.00
3.00
instead of all amounts being right-justified with all the decimal points lining up vertically.

 

[Richard Schollar]

Hi Rick

Have you tried explicitly right-justifying the relevant cells? eg:

Range(Whatever).HorizontalAlignment = xlRight

Richard

 

[Norie]

rick

I don't think this is possible without padding out the values with spaces.

And even then you might not get the required look, depending on what font you are using.

 

[ricksimm]

I think you are right. If I change from "###.00" to "000.00" they line up correctly but i get a list like -

125.00
025.00
175.00

This is not exactly the way to do things. It seems that Excel could surely handle this but it doesn't look as if it can. Any other suggestions?

 

[pgc01]

Hi Rick

Please try:

"???.00"

? is like # but leaves the space.

Hope this helps
PGC

 

[ricksimm]

That gave me -

???125.00
???25.00
???175.00

Thanks for your suggestion.

 

[pgc01]

Hi again

The ? as format is not to be used in the format function in vba. You have to use it in the custom number format of the cell.

By the way, why don't you just right-align the cells as Richard proposes?

HTH
PGC