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

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
168
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

portews

Active Member
Joined
Sep 4, 2009
Messages
335
How about naming the range you want? If you insert columns, the name will stay with the initial cell(s) you named.
 

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
168
Office Version
  1. 365
Platform
  1. Windows
How would I refer to the range in that snippet of code I posted? Let’s say the range name was “wiringPO”.
 

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
168
Office Version
  1. 365
Platform
  1. Windows
Also, this data is all coming from a table. Would that make a difference in naming ranges or is there a way to use table references to refer to the columns?
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,322
Office Version
  1. 2016

ADVERTISEMENT

instead of
Cells(i, 24).Value
if you need column 24 (column X) is fixed:
use:
Cells(i, "X").Value
 

portews

Active Member
Joined
Sep 4, 2009
Messages
335
VBA Code:
Range("Jobs!wiringPO").Value
A quick test says it works on tables and non-tables equally.

If you want to set the range name programmatically, try this:

VBA Code:
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Jobs")
Set Rng = ws.Range("X" & i)
ThisWorkbook.Names.Add Name:="wiringPO", RefersTo:=Rng
 

sspatriots

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

ADVERTISEMENT

Makes sense. I will give it a try today
 

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
168
Office Version
  1. 365
Platform
  1. Windows
Sorry, quick question. In your reference below, is "X" referring to column "X"? Just asking because if I do it like you did, but use "Cells(i, "X").Value" instead will it still work? I guess I'm not clear on how to present what is inside the parenthesis. Seems like they would have two different meanings, or shall I say behave differently.

(i, "X") versus ("X" & i)


VBA Code:
Set Rng = ws.Range("X" & i)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,986
Office Version
  1. 365
Platform
  1. Windows
Sorry, quick question. In your reference below, is "X" referring to column "X"? Just asking because if I do it like you did, but use "Cells(i, "X").Value" instead will it still work? I guess I'm not clear on how to present what is inside the parenthesis. Seems like they would have two different meanings, or shall I say behave differently.

(i, "X") versus ("X" & i)


VBA Code:
Set Rng = ws.Range("X" & i)
In VBA there are a few important things to remember:
- Any strings enclosed in double-quotes is treated as literal text
- Any strings not enclosed in double-quotes are treated as variables (and it will return the value of the variable)
- To combine strings and variables, you use the ampersand ("&")

And there are two different ways to refer to ranges.
You can use RANGE, in which the format is "column letter + row number", like this:
Range("X10")
which would reference cell X10

If the row number was a variable, like "i", where the value of "i" is 10, then you could use:
Range("X" & i)

You can also refer to ranges using CELLS. The format of cells is Cells(row, column)
One advantage to using Cells is that you can use either the column letter, or column index number (i.e. "A"=1, "B"=2", "C"=3, etc).
This is nice if you are looping through columns.
So, if you wanted to refer to cell X10, you could use either:
Cells(10, "X")
or
Cells(10, 24)

If, as in the previous example, you had the variable "i" set to 10, then you could also use:
Cells(i, "X")
or
Cells(i, 24)

Does that help clarify things for you?
 

sspatriots

Board Regular
Joined
Nov 22, 2011
Messages
168
Office Version
  1. 365
Platform
  1. Windows
Thanks for this. I will read this and re-read it to try and ingest it all. I should have included more of my code in my original post. My bad. In my case there is a statement that has it loop through "For i = 3 to LstRow". So this is a better example of what I need to convert to the named ranges. There must be 20 columns in this code I need to name.

VBA Code:
Private Sub UserForm_Activate()
Me.txtJobName.Locked = True ' set this property in Textbox Properties or with VBA like this
'Me.txtJobName.Text = Sheets("Jobs").Range(ActiveCell.Address)
Me.txtJobName.Text = Sheets("Quick Search Job Status").Range("B3").Value

Dim LastRow As Long 'Added, SPS, 06/10/22
Dim i As Integer 'Added, SPS, 06/10/22

Dim job_name As String
job_name = Trim(txtJobName.Text)
LastRow = Worksheets("Jobs").Cells(Rows.Count, 2).End(xlUp).Row

For i = 3 To LastRow

If Worksheets("Jobs").Cells(i, 2).Value = job_name Then

cboJobStatus.Text = Worksheets("Jobs").Cells(i, 3).Value 'GOOD
txtG2PM.Text = Worksheets("Jobs").Cells(i, 4).Value 'GOOD
 
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,168,160
Messages
5,857,718
Members
431,892
Latest member
Chucklez

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