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

[TABLE="width: 500"]
<tbody>[TR]
[TD]$ 5.00[/TD]
[/TR]
[TR]
[TD] $4.00
$3.00[/TD]
[/TR]
[TR]
[TD]$ 7.00[/TD]
[/TR]
</tbody>[/TABLE]


I want to see it as this

[TABLE="width: 500"]
<tbody>[TR]
[TD]$ 5.00[/TD]
[/TR]
[TR]
[TD]$ 4.00
$ 3.00[/TD]
[/TR]
[TR]
[TD]$ 7.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[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