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

 

[RoryA]

Something like this:

Dim tb as ListObject
set tb = Activesheet.listobjects("tableName")

For i = 1 to tb.databodyrange.rows.count

If tb.Listcolumns("Job name").databodyrange.cells(i).Value = job_name Then

cboJobStatus.Text = tb.Listcolumns("Job Status").databodyrange.cells(i).Value 'GOOD
txtG2PM.Text = tb.Listcolumns("G2PM").databodyrange.cells(i).Value 'GOOD

obviously use your table and column names.

 

[sspatriots]

Something like this:

Dim tb as ListObject
set tb = Activesheet.listobjects("tableName")

For i = 1 to tb.databodyrange.rows.count

If tb.Listcolumns("Job name").databodyrange.cells(i).Value = job_name Then

cboJobStatus.Text = tb.Listcolumns("Job Status").databodyrange.cells(i).Value 'GOOD
txtG2PM.Text = tb.Listcolumns("G2PM").databodyrange.cells(i).Value 'GOOD

obviously use your table and column names.

I will give this a try. Thanks.

 

[sspatriots]

Couple of questions.

Do I place this code under my Userform code?

The Userform is launched from a worksheet that the table is not on, will the code still work for my Userform if the worksheet that launches the form is different from the one the table is on?

To make sure I understand correctly, this code set up will populate my Userform?


Sorry for all the questions, but just want to understand before I try and put this in my workbook and do something wrong to mess it up. Thanks, SS

 

[sspatriots]

This is what I have so far, but nothing happens with the Userform. It gets an "Opject required" error message 424 at the line that starts with cboJobStatus.Text.

Sub Populate_Job_Status_Form()

Dim ws As Worksheet   'Added SPS,06/16/22
Dim tb As ListObject

Set ws = Worksheets("Jobs")   'Added SPS,06/16/22, worksheet the table is on

ws.Activate

Set tb = ActiveSheet.ListObjects("G2JobList")

For i = 1 To tb.DataBodyRange.Rows.Count

If tb.ListColumns("Job_Name").DataBodyRange.Cells(i).Value = job_name Then

Job_Status.Show

cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD

End If


Next


End Sub

 

[sspatriots]

This is what I have so far. Not even sure it will work. It stops with a Compile error: End With without With at my "End With" line of code. If anyone can chime in here, I would appreciate it. Thanks, SS

Sub Populate_Job_Status_Form()

Dim wb As Workbook

Dim ws As Worksheet   'Added SPS,06/16/22

Dim tb As ListObject

Dim Frm As Object    'UserForm

Dim job_name As String

Set wb = ThisWorkbook

Set ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on

Set tb = ws.ListObjects("G2JobList")

Set Frm = Job_Status


With Frm

For i = 1 To tb.DataBodyRange.Rows.Count

If tb.ListColumns("Job_Name").DataBodyRange.Cells(i).Value = job_name Then   

job_name = Trim(txtJobName.Text)

cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD

End With


Job_Status.Show


End If


Next


End Sub

 

[RoryA]

Move the With frm line after the If… line

 

[sspatriots]

Thanks, I'll give it a go when i get back to the office in the morning. Fingers crossed

 

[sspatriots]

I made the change this morning. Now getting a Run-time error '424': Object required message at the following line:

job_name = Trim(txtJobName.Text)

My original code pointed to cell B3 on worksheet "Quick Search Job Status" (ComboBox1), that populated the first Textbox (named: txtJobName) in my UserForm (named: Job_Status). So I think maybe I'm still missing something that gets it to populate the UserForm (Job_Status) and whatever is causing the error message mentioned above.

Sub Populate_Job_Status_Form()


Dim wb As Workbook

Dim ws As Worksheet   'Added SPS,06/16/22

Dim tb As ListObject

Dim frm As Object    'UserForm

Dim job_name As String

Set wb = ThisWorkbook

Set ws = wb.Sheets("Jobs") 'Added SPS,06/16/22, worksheet the table is on

Set tb = ws.ListObjects("G2JobList")


Set frm = Job_Status


For i = 1 To tb.DataBodyRange.Rows.Count

If tb.ListColumns("Job_Name").DataBodyRange.Cells(i).Value = job_name Then            'temporary until I figure out where job_name comes from


With frm

    job_name = Trim(txtJobName.Text)

    cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
    txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD

End With


Job_Status.Show



End If


Next


End Sub

 

[RoryA]

Change this:

With frm

    job_name = Trim(txtJobName.Text)

    cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
    txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD

End With


Job_Status.Show

to this:

With frm

    job_name = Trim(.txtJobName.Text)

    .cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
    .txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD
    .Show
End With

Note the dots before the names of the controls on the form.

 

[sspatriots]

Well that did something. It did populate the Textbox (named: txtJobName). However, I didn't get anything with regards to populating the two fields below:

    .cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
    .txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD

BTW: I appreciate your patience with me on this. A lot to learn here.