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

 

[portews]

How about naming the range you want? If you insert columns, the name will stay with the initial cell(s) you named.

 

[sspatriots]

How would I refer to the range in that snippet of code I posted? Let’s say the range name was “wiringPO”.

 

[sspatriots]

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]

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

 

[portews]

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:

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]

Makes sense. I will give it a try today

 

[sspatriots]

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)

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

 

[Joe4]

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)

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]

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.

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