VBA Code for Number Format - Run-time error '1004' Unable to set the Number Format property of the Range class

[OilEconomist]

Hello and thanks in advance for any assistance you can provide. Via Google search I have searched this forum and others, but my sheet is not protected and this code has previously worked I just can't find where I have used it previously.

Why am I getting the error "Run-time error '1004' Unable to set the Number Format property of the Range class" for the following line:

Range("C" & i).NumberFormat = "[>=1000000] #,##0.0,,""m"";[>0] #,##0.0,""k"

in the following code and how do I fix it?

Public Sub TestNF()
 Dim i As Long
 i = 71
 Range("C" & i).Value = 12345684
Range("C" & i).NumberFormat = "[>=1000000] #,##0.0,,""m"";[>0] #,##0.0,""k"
End Sub

But why do I not get the same error in the following code:

Public Sub TestNF()
 Dim i As Long
 i = 71
 Range("C" & i).Value = 12345684
 Range("C" & i).NumberFormat = "#,##0.00"
End Sub

when I use the following line of code instead

Range("C" & i).NumberFormat = "#,##0.00"

Once again thanks for your assistance.

 

[JoeMo]

You're missing a closing quote mark at the end of the offending line (added below).

Range("C" & i).NumberFormat = "[>=1000000] #,##0.0,,""m"";[>0] #,##0.0,""k"""

 

[OilEconomist]

You're missing a closing quote mark at the end of the offending line (added below).

Range("C" & i).NumberFormat = "[>=1000000] #,##0.0,,""m"";[>0] #,##0.0,""k"""

Thanks @JoeMo. That worked.

 

[JoeMo]

Thanks @JoeMo. That worked.

You are welcome - thanks for the reply.