Is it possible to write code to modify code for new columns?

[sspatriots]

I have this code that has a lot of references to many columns in it.

For example:

Worksheets("Jobs").Cells(i, 24).Value

The problem I will be facing soon is I will most likely have to insert 3 new columns on the "Jobs" worksheet that will be in front of many of those references. Just wondering if there is a way write something that will take for example ".Cells(i, 24).Value" and change it to ".Cells(i, 27).Value" or ".Cells(i, 30).Value" and change it to ".Cells(i, 33).Value" for all references with a column greater than 10.

Just curious as to how doable this kind of thing is.


Thanks, Steve

 

[sspatriots]

Sorry for all the questions. If I wanted to do all this and keep the integrity of using just the table header names in the table (in lieu of actual column numbers) so that the range expands and contracts with the table when items are added or removed within the table, what would I need to do to my code and the line below to make all this work for a table header named "G2PM"? I've been tinkering with this for a while, but coming up empty. That is, I think I would like to use the table column references versus the named range method if it doesn't require major changes to the first half of my code that had posted this morning.

txtG2PM.Text = Worksheets("Jobs").Cells(i, 4).Value 'GOOD

 

[sspatriots]

If I try and replace...

txtG2PM.Text = Worksheets("Jobs").Cells(i, 4).Value

with...

txtG2PM.Text = Worksheets("Jobs").Range("Jobs!G2_PM").Value

In my code, then I lose the looping that is in the code. Not clear on how to use any defined name range with what I have in my post #10 of this thread.

 

[sspatriots]

I've managed to create a named range for every column on that table. I just need some assistance with referencing the named ranges in my code that has a loop in it. Anyone?

Thanks, SS

 

[Joe4]

I am not particularly a fan of using a named range approach, but if you want to loop through a list of named ranges, you may be able to use the method shown here: How to loop through all named ranges in a sheet?

 

[sspatriots]

I wouldn't no where to begin with that code. I thought there would be a good way to change my code such that if I were to insert a column at some point that I wouldn't have to shift all my column references in my code. I probably have over 100 of them. Some referenced multiple times. I may have to give up on this one. Thanks.

 

[Joe4]

I wouldn't no where to begin with that code. I thought there would be a good way to change my code such that if I were to insert a column at some point that I wouldn't have to shift all my column references in my code. I probably have over 100 of them. Some referenced multiple times. I may have to give up on this one. Thanks.

The second post in that code shows you exactly how to loop through the named ranges on a sheet.
It is actually quite and simply code, and should be easily adaptable.
What part of it is throwing you for a loop?

 

[sspatriots]

I was hoping to be able to modify the code that i put in Post number 10 and use named ranges for my columns in lieu of actual column numbers. I couldn't see any way to do that. So, I think I've come up with another way to do this by just having all the lookup values show up on a worksheet in hidden columns and then pulled back into the userform from there.

 

[Joe4]

I was hoping to be able to modify the code that i put in Post number 10 and use named ranges for my columns in lieu of actual column numbers. I couldn't see any way to do that. So, I think I've come up with another way to do this by just having all the lookup values show up on a worksheet in hidden columns and then pulled back into the userform from there.

The code in post 10 shows a "For" loop looping through rows.
The code in the 2nd post in the link I provided shows a "For" loop, looping through named ranges, which is what I thought you were after.

 

[RoryA]

If it's in a table, you should use the table object in your code. For example:

Activesheet.listobjects("tableName").Listcolumns("Column name").Databodyrange.cells(x).Value = "whatever"

 

[sspatriots]

If it's in a table, you should use the table object in your code. For example:

Activesheet.listobjects("tableName").Listcolumns("Column name").Databodyrange.cells(x).Value = "whatever"

I did come across the table reference code that you are speaking of in my search. My question is how do you make that work with the loop in my code in post #10? Thanks, SS