How to make my code work with table column names

So I inherited this workbook from a coworker who knew waaaaaay more about VBA than I do. I just need to be able to add columns to the "compliance tracker" tab while still having my code work as a table since I think that is probably easiest to code for. My workbook is attached.

I am more than happy to take any suggestions to make this easier. I appreciate all your help!!!

Thanks!
Dave


Compliance Tracker - Corporate Draft - updating v2.xlsm

Originally Posted by Richard Buttrey

Hi Dave,

I'm not sure you need to do anything. An Excel Table range will automatically adjust as you add a new column immediately to the right of column U. That's what Tables do.

That said I note that Table 1 is defined as A9:U1048576 which suggests that at some time the very last row has been included in the table so it would be worth tidying that up unless you really do want 1 million plus rows. Convert it to a range and then recreate it with the Insert table option again.

Personally I rarely use the Table functionality. I just don't like the syntax of formulae within a table and find them more difficult to read. I generally use dynamic range names with regular ranges whenever I need to ensure that additions to a range are automatically included in the range definition.

I also note that there seems to be an awful lot of repetition in the GenerateEmails procedure.
The only lines that seems different in all the Case statements are ones beginning
toemail = "GetMail....etc
subemail = "CorpTaxID:......." etc.

The rest could be set once.

Hi Richard

My problem is that when I add columns, my generate email macro doesn't work. I don't even come close to knowing enough about VBA how to fix that.

Thanks!

Originally Posted by Richard Buttrey

Would you clarify what you mean by 'doesn't work'.

The macro simply sends an email with something like
Coprtax ID: C1000d204 Entity Name: Entity 5 is in Prep.
in the body of the email.

I can't see how adding additional columns is of any relevance.

However doing an End Down on any of the empty columns arrives at row 1,048,555. This is the case with column R and in the macro column R is used to determine the last row and hence the number of iterations the macro must process. If column R only contains "Not Started" or blank then the macro will take a long time to process all 1 million + rows which may be what you mean by 'not working'. The fact that you may also have added columns I don't believe is the problem here.

I suggest that you select all rows from row 199 down to the bottom and delete them all, then try the macro.

However clarify what you mean by doesn't work if this doesn't change anything.

When i add an new columns before column R and hit generate email, say when I have switched a status to "In-prep", it just says process complete and does not actually generate an email. I think that's because the code is made so it looks at a specific column (i am not good at VBA at all). thats the problem I am having.

Originally Posted by xlnitwit

Hi,

Once you have amended the table size as Richard suggested, you might simply loop through the table rows and refer to the columns by name like this

Please Login or Register  to view this content.

for all the other column references you would use the same syntax and simply replace the blue section with the relevant column name.

is there a way i could have cells with those names in them, then link to those cells within the blue text you put so that i could easily change the names in the cells if i want to change the column that is being looked at?

Originally Posted by Richard Buttrey

Ahh!

That's clearer now. I had assumed you were adding columns to the right of the last column U. Adding columns before column R of course shifts all columns across and what was column R now becomes column S - assuming you just add a single column. Unfortunately the reference to column R is hard coded in the macro in the line

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

and so the macro never finds any of the Case statements.

All of which is an excellent example of why it's never good practice to hard code sheet names or cell and range addresses in the macro. You should always use range names.

So first define the name 'Status' as R8

Now change the first instruction in the macro to

Please Login or Register  to view this content.

and the second instruction to

Please Login or Register  to view this content.

Thank you so much Richard! I will try this code tonight!