Excel Geeking: When List Object Columns Attack, or Why Won’t This Calculated Column In My List Object Table Calculate Anymore

I know the title of this post is too long. But it captures the essence of what I’m trying to figure out.

I’m sure this has happened to someone else, and maybe even someone else who has figured out how to correct it. If you have, let me know, leave a comment below, send a smoke signal, anything to help me close the loop on this thing.

Here’s the problem: in a list object, or table as they’re often called, on occasion a field that is normally calculated stops doing just that. More clinically, the formula replication functionality in Excel coughs twice, falls down in the dusty road of the information highway, and gives up the ghost. Not on all tables, mind you. Not even on all columns in the same table. In specific columns only.

I’m on the hunt to figure out why.

The primary problem in searching for an answer is that you can’t quite find what you’re looking for through Google. Or maybe its just me, and that I could very easily suck and searching the interwebs. When I try searching for words such as “list object calculated column not working”, or “replicate formula no longer working”, I always find myself awash in a sea of posts about how to change the setting in Options for replicating formulas. That’s not what I need. I also find a lot of posts on how to turn Calculation back on in a workbook. Not what I need. I know these two things already. I need something else.

I checked to make sure that the cell isn’t formatted for Text. If you’ve ever formatted a column as Text and tried to change the formatting back to Number or General, you know that Excel can occasionally develop amnesia about how formulas work in those cells. Once you turn a cell to Text, changing it back is a pain in the butt given that Excel likes to keep treating formulas as if they’re Text. No, that’s not the issue I have here.

I tried adjusting the Options settings. I turned the Replicate Formulas option off, then back on. I had a hope that this would essentially pop the clutch on these calculated columns. No dice. I tried doing it through the code. Also no dice.

I could have spent all day rotting this thing out, but guess is that is never actually find it. It was simpler to build a new table with the same columns and move my data into it. Which is what I did.

Still, I figure there has to be a means of resetting a calculated column. Anyone? Anyone? Bueller? Bueller?

Advertisements

Excel Geeking: Using Special Characters in ListObject Column Names in VBA

This is a short one, but a REALLY specific one.

Recently (i.e., today) I was tinkering with some List Objects, a.k.a. “tables” in VBA. All I was trying to do was to clear the contents of a column. Easy enough. It looked something like this:

Sheet1.Range("Table1[# Resource Req'd]").ClearContents

No Excel love. It kept throwing me an error. Specifically, error 1004, “Method ‘Range of object ‘_worksheet’ failed”.

What could I be doing wrong?

I started with the two best posts on Excel tables. First the Spreadsheet Guru’s post giving an introduction to tables. I couldn’t quite find what I was looking for, so I jumped over to Jon Peltier’s post on tables. It’s a little more detail than the overview by the Spreadsheet Guru. Unfortunately I couldn’t find what I needed there either, but it did give me an idea as to the problem.

You see, I have two “special” characters in the column header. Specifically, the hastag (#) and the apostrophe (‘). I figured my error had something to do with these, but neither post, nor a cursory Google search turned anything up when using them in VBA.

So I went the old school route. I recorded a macro wherein I highlighted the column in the table and then used Right-Click > Clear Contents.

What I found was that the code added an extra apostrophe before each special character. I’ve never seen this or needed to use it in any other code I’ve written for Excel, so maybe it’s specific to List Objects only. Then again, maybe not. Truth be told, I wasn’t interested in the history behind the extra apostrophe, I just needed the damn thing to work.

So now the code looks like this:

Sheet1.Range("Table1['# Resource Req''d]").ClearContents

And it works beautifully.