Thread: Select column from table using variable table name -- VBA

Hello. I am trying to select various table columns or cells for editing using VBA. In my script, the user enters a department name and the month. These variables are then combined to create the variable tableName. The script then selects the appropriate data, creates a table, and names it tableName. So far so good.

Next, I want to be able to select columns to enter formulas and formatting. For example, I tried using this code to calculate a percentage column:

Range(tableName & [% Spent]).FormulaR1C1 = _
        (tableName & [[#This Row],[Encumbrances]] + tableName & [[#This Row],[YTD]]) / tableName & [[#This Row],[REV BUD]]

but I get a "Type mismatch" error.

Elsewhere, I want to select a few columns and format them. My selection statement reads:

Range(tableName & [[#All],[Encumbrances]:[REV BUD]]).Select

but again, it doesn't work.

Would using ListObjects terminology help?

Thanks a lot,
JJ

You need to use quotes not square brackets when concatenating the table name and columns:

Range(tableName & "[#All],[Encumbrances]:[REV BUD]").Select