Type Mismatch

[Karen53]

Hi,

I am getting an error 13: Type Mismatch and don't understand why. Would
someone clarify this for me?

Sub SaveItems(AmountCol, Choice, ItemAmount)

Dim ws As Worksheet
Set ws = Tablespg

With ws

With .Range(AmountCol & Choice)
.Value = Val(ItemAmount)
.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* " -
"??_);_(@_)"
End With

End With

End Sub

The error occurs at the .NumberFormat statement. ItemAmount is a value
obtained from a textbox then converted to a number in the .Value statement.

 

[Bob Phillips]

What is in AmountCol, Choice and ItemAmount. My guess is that one of those
is invalid

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andrew Taylor]

The minus sign in the .NumberFormat line makes
no sense - the type mismatch is complaining about
that, because you're trying to subtract one string
from another. You just need something like:

..NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*??_);_(@_)"

 

[Jean-Yves]

Hi Karen,

After test, it comes from the string format argument itself.
OK "_(* #,##0.00_);_(* (#,##0.00))"
Not OK "_(* #,##0.00_);_(* (#,##0.00);_(* " - "??_);_(@_)"

Try recording a macro to get the format argument correctly set.
Regards

JY

 

[Karen53]

Thanks to all. I got it working.