VBA need to format of Accounting

[mduntley]

I found this code to make it so a cell with multi lines to easy be the format, but I am trying to incorporate to view it as accounting as the other cell that has single lines that have accounting format. I tried to do it as formatcurrency but it does not look the ones below or above. Can this be done?

Public Function FORMATLINES(cell As Range) As StringDim data() As String, i As Long
data = Split(cell.Text, vbLf)
For i = 0 To UBound(data)
    If IsNumeric(data(i)) Then data(i) = FormatNumber(data(i), 2, vbTrue, vbFalse, vbTrue)
Next
FORMATLINES = Join(data, vbLf)
End Function

 

[Norie]

Try using Format instead of FormatNumber.

Public Function FORMATLINES(cell As Range) As StringDim data() As String, i As Long
    data = Split(cell.Text, vbLf)
    For i = 0 To UBound(data)
        If IsNumeric(data(i)) Then data(i) = Format(data(i), "Currency")
    Next
    FORMATLINES = Join(data, vbLf)
End Function

 

[mduntley]

got it to work, but my chart isnt in accounting form. it has this

$ 5.00

$4.00 $3.00

$ 7.00

<tbody>
</tbody>

I want to see it as this

$ 5.00

$ 4.00 $ 3.00

$ 7.00

<tbody>
</tbody>

 

[Norie]

How did you get it to work?

 

[mduntley]

I got the vba to work because it was this:

Public Function FORMATLINES(cell As Range) As StringDim data() As String, i As Long

When it needs to be

Public Function FORMATLINES(cell As Range) As String
Dim data() As String, i As Long

 

[Norie]

I assumed that was a typo I've seen similar things happen when code is copied to a post.

Once you fixed that did you try my suggestion of using Format instead of FormatNumber?

 

[mduntley]

I assumed that was a typo I've seen similar things happen when code is copied to a post.

Once you fixed that did you try my suggestion of using Format instead of FormatNumber?

yes, i got it to work, thanks