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

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
I have this code that has a lot of references to many columns in it.

For example:

VBA Code:
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

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
txtG2PM.Text = Worksheets("Jobs").Cells(i, 4).Value 'GOOD
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
If I try and replace...

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

with...

VBA Code:
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

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
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
 

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,905
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,905
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,947
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
If it's in a table, you should use the table object in your code. For example:

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

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
161
Office Version
  1. 365
Platform
  1. Windows
If it's in a table, you should use the table object in your code. For example:

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,565
Messages
5,854,473
Members
431,652
Latest member
monpuipui

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top