Inserting Column VBA

[Noz2k]

Is there a way to auto update your VBA code when you insert a column into excel?

So that everything after moves along one place??

So everything referencing column "J" would change to column "K"?
and the same with offsets?

Or would I have to go through and update everything manually?

 

[Jonmo1]

Do you have unique headers in your columns?
Like A1 is Name, B1 is Birthday, C1 is StartDate, etc...

If so, you can use the find method to find the column Header and assign that number to a variable..Then refer to that variable in the range or columns command...

For example...

ColA = Rows("1:1").Find(What:="Name", After:=Range("A1")).Column
Cells(2, ColA).Value = "This is the Name Column"

Hope that helps.

 

[Noz2k]

all my columns have headings in row 1.

I'm not quite sure how to implement that now though. I think I should have used the column headings to reference the column when I began. (Note to self in future).

All the corresponding textboxes/comboxes are named "???ColumnHeading" as wellso, I guess I could have used that to make things easier too.

 

[Jonmo1]

Yep, call me captain HindSight....LOL..

This is the type of thing that works great while writing the code in the first place.
Re-Writing existing code to impliment this funcitonality is a whole other ball game.
It will take some work, but will be worth it in the end.

 

[Noz2k]

Yeah, unfortunately I'd never used VB before starting this project a week or 2 ago. It's only now that the criteria has slightly changed ie another column is needed in the middle of the table that it's a problem.

You live and learn though I guess.