Type Mismatch using NumberFormat with DataBodyRange

EthWa021

New Member
Joined
Jan 10, 2019
Messages
4
In the past I've done .Range.NumberFormat no issue, but having a problem using with .DataBodyRange . The following is what I have (with generic labeling):

Code:
'Code defining variables, etc

with ws.listobjects(1)
    .DataBodyRange(r,c).NumberFormat = "0"   'Type Mismatch Error occurs
end with

Thank you very much.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,073
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Welcome to the forum.

How are r and c declared and what values do they have?
 

EthWa021

New Member
Joined
Jan 10, 2019
Messages
4
Thanks. First time posting, millionth time reading/searching.

r and c are defined earlier in the code, to find the correct row/column within the table - their values are relative to the table, not absolute to the spreadsheet.
They are both dim as integers.

I think in this exact instance they are 20 and 25, and my table is 22x25.
Made sure to have error handlers prior in case I get values "outside" the table.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,328
Office Version
  1. 365
Platform
  1. Windows
I think you need to check what the values of r and c are when you get the error.
 

EthWa021

New Member
Joined
Jan 10, 2019
Messages
4

ADVERTISEMENT

Hindsight - I made this thread too vague. I apologize.
Foresight - just copy and paste code :(

Code:
'rest of code defining variables
r = 20
c = 25

'table dimensions 22x25

with ws.ListObjects(1)


    .DataBodyRange(r, c).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* " - "??_);_(@_)"    'type mismatch error

end with

after realizing the "simplified" version of code actually worked. ugh. *Dunce Hat*
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,073
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
That explains a lot! You need to double up the quotes in the number format string:

Rich (BB code):
"_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,857
Messages
5,766,788
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top