Type Mismatch using NumberFormat with DataBodyRange

[EthWa021]

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 defining variables, etc

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

Thank you very much.

 

[RoryA]

Welcome to the forum.

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

 

[EthWa021]

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]

I think you need to check what the values of r and c are when you get the error.

 

[EthWa021]

Hindsight - I made this thread too vague. I apologize.
Foresight - just copy and paste 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]

That explains a lot! You need to double up the quotes in the number format string:

"_($* #,##0.00_);_($* (#,##0.00);_($* "" - ""??_);_(@_)"

 

[EthWa021]

DUH. Rookie mistake.

Thank you so much. Case closed.