type mismatch error question

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
All,

I'm getting a "Type Mismatch" error trying to run this code...anybody see what's wrong with it?
------------------------
Sub FormatChange()
If Cells("Criteria!E22") = "Sales" Then
Worksheets("Test").Range("C6:J18").NumberFormat = "$#,##0($#,##0)"
Else
Worksheets("Test").Range("C6:J18").NumberFormat = "0.00"
End If
End Sub
-------------------------

I have a list box whose criteria range points to a cell on another page, thus when that cell (on sheet Criteria, cell E22) reads Sales, I want the number format of the range on the page Test to be a dollar format, and otherwise just a 2-decimal number format. Can't seem to figure out what the problem is, though.

Thanks,

Erich
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
Hey, Oaktree,

Tried that, and now I get a "Application-defined or object-defined error".

???

Erich
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Erich

Is Criteria the name of a worksheet?
Code:
Sub FormatChange() 
If Worksheets("Criteria").Range("E22").Value = "Sales" Then 
     Worksheets("Test").Range("C6:J18").NumberFormat = "$#,##0($#,##0)" 
Else 
     Worksheets("Test").Range("C6:J18").NumberFormat = "0.00" 
End If 
End Sub
 

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129

ADVERTISEMENT

That worked, thanks, Norie! I should have thought that through a little more...

Cheers,


Erich
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Try Norie's suggestion.

Also, I'm guessing that you probably want $#,##0;($#,##0) instead of $#,##0($#,##0)

(note the semicolon in the former)
 

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129

ADVERTISEMENT

haha, thanks, Oaktree. I was just trying to figure out why that was wrong.

Cheers,

Erich
 

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
Hm. This still isn't quite working...it'll retain the format set first, but if "Sales" is NOT selected, it won't change it to the "0.00" format.

Any ideas?

Erich
 

Erich Duff

Board Regular
Joined
Apr 20, 2005
Messages
129
Don't know if this is the best way to fix, but it seems to work...I split this into two macros that call each other as their "Else" entries, and now the format changes appropriately.

Erich
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Sorry, I cannot replicate your problem.

Sub FormatChange()
If Worksheets("Criteria").Range("E22").Value = "Sales" Then
Worksheets("Test").Range("C6:J18").NumberFormat = "$#,##0;($#,##0)"
Else
Worksheets("Test").Range("C6:J18").NumberFormat = "0.00"
End If
End Sub


Works as intended for me...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,842
Members
412,420
Latest member
grace_abar
Top