Number formatting Question

[cmmadn]

Dear sirs,

I am hoping someone can help, I am trying to correctly format my monetary value so that it shows any required 00's i.e £25.00 or £24.50. Using the code below the end zeros are dropped £25 or £24.5.

I am finding this desperately frustrating as I cannot solve this issue.

I would be most grateful to anyone who can point me in the right direction.

If rngCashBalance < 0 Then
   wskClientValuation.Range("b" & (intStartLine + 3)) = "Please send a cheque for " _
    & Application.RoundDown((rngClient.Offset(0, 16) - (rngClient.Offset(0, 16) * 2)) + rngClient.Offset(0, 18), 2) & " to cover the fees now due plus the amount overdrawn."
Else
   wskClientValuation.Range("b" & (intStartLine + 3)) = " As there is insufficient cash on your account, please forward a cheque for the remaining balance of " _
                        & Application.RoundDown(rngClient.Offset(0, 18) - rngClient.Offset(0, 16), 2)

Regards

Andrew

 

[VoG]

Try

If rngCashBalance < 0 Then
   wskClientValuation.Range("b" & (intStartLine + 3)) = "Please send a cheque for " _
    & WorksheetFunction.Dollar(Application.RoundDown((rngClient.Offset(0, 16) - (rngClient.Offset(0, 16) * 2)) + rngClient.Offset(0, 18), 2), 2) & " to cover the fees now due plus the amount overdrawn."
Else
   wskClientValuation.Range("b" & (intStartLine + 3)) = " As there is insufficient cash on your account, please forward a cheque for the remaining balance of " _
                        & WorksheetFunction.Dollar(Application.RoundDown(rngClient.Offset(0, 18) - rngClient.Offset(0, 16), 2), 2)

 

[cmmadn]

Hi Vog,

Thanks for this, can you tell me how to get £ (GBP) rather than a $ (USD) sign

I have tried .Pound and .Sterling

Thanks

Andrew

 

[VoG]

Hi

DOLLAR() should format with whatever your currency symbol is set to in Windows. For me it gives a £ sign.

 

[cmmadn]

Hi Vog,

I am not sure if it makes a difference but I am using a MAC

Regards

Andrew

 

[VoG]

Sorry I don't know anything about MACs. Try this alternative

If rngCashBalance < 0 Then
   wskClientValuation.Range("b" & (intStartLine + 3)) = "Please send a cheque for £" _
    & Format(Application.RoundDown((rngClient.Offset(0, 16) - (rngClient.Offset(0, 16) * 2)) + rngClient.Offset(0, 18), 2), "0.00") & " to cover the fees now due plus the amount overdrawn."
Else
   wskClientValuation.Range("b" & (intStartLine + 3)) = " As there is insufficient cash on your account, please forward a cheque for the remaining balance of £" _
                        & Format(Application.RoundDown(rngClient.Offset(0, 18) - rngClient.Offset(0, 16), 2), "0.00")

 

[cmmadn]

Appears to be perfect! thanks