VBA NumberFormat Implementation

Question

NumberFormat seems to be a straightforward function but I can't get it to work is a rather simple case. In the code below VBA tells me "Type Mismatch".

For j = 1 To last_column + 1
    For f = 2 To total_tranches + 1
        If allocation(f - 1, j - 1) = Empty And j <> 1 Then
            Allo1.Cells(AlShares.Row - 1 + f, j + 1) = 0
        Else
            Allo1.Cells(AlShares.Row - 1 + f, j + 1) = allocation(f - 1, j - 1)
            If j = 1 Then Allo1.Cells(AlShares.Row - 1 + f, j + 1).IndentLevel = 1
            If j = 2 Then Allo1.Cells(AlShares.Row - 1 + f, j + 1).NumberFormat = "_($*#,##0_);_($*(#,##0);_($*" - "??_);_(@_)"
        End If
    Next f
Next j

And if I replace specific formatting with "Accounting" it just does not work. Please help!

Possible duplicate of [Escape double quote in VB string](https://stackoverflow.com/q/4835691/11683) — GSerg, Apr 07 '18 at 19:38

score 2 · Answer 1 · answered Apr 07 '18 at 19:22

2

You are asking VBA to subtract 2 strings "...($*" - "??_.." so that is the type mismatch.

answered Apr 07 '18 at 19:22

Charles Williams

23,121
5
38
38

Interesting way of describing the problem. – Apr 07 '18 at 19:28
Thank you it worked, and my other problems went away as another part of the code was overwriting your good formatting method!! – MaxF Apr 07 '18 at 20:07

VBA NumberFormat Implementation

1 Answers1