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]

Are you sure those cells are populated in row 1 of the table?

By the way, why are you showing the form in a loop?

 

[sspatriots]

I just need this code to take what is in cell B3 of one worksheet and look it up in the table that is in another worksheet and populate information in that row from various columns on that table based on that look up. Not sure if I'm doing this right or not. After that, I need the ability to make changes to the table from the same UserForm and update those values back in the table. The loop was from the original code that I used that was working, but had all the hard column references in it.

 

[RoryA]

Ignore my question about the loop - I had forgotten this part was inside an If clause.

Is there definitely data in the two cells that are supposed to be populating the other controls? If so, you should either see that or be getting an error of some kind.

 

[sspatriots]

There is data in those two cells of the table corresponding to that job name that shows up. No error messages. Does it make a difference if the headers of the table are in row 2 and the data starts at row 3?

 

[RoryA]

No, the code is iterating through the table - it doesn't matter where it is.

 

[RoryA]

Can you post your current code again? Looking back at the last version you posted, you only set the jobname variable inside the If clause so the code will only match a blank cell.

 

[sspatriots]

No, the code is iterating through the table - it doesn't matter where it is.

Does it matter what the column number that the "Job_Name" is under? It falls under column B on the worksheet with the table.

 

[sspatriots]

Sure...

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
    .Show
    
End With


End If


Next


End Sub

 

[RoryA]

OK, change that to this:

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
   Dim i As Long
   
   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
   
      job_name = Trim(.txtJobName.Text)
      
      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
         
            .cboJobStatus.Text = tb.ListColumns("Job_Status").DataBodyRange.Cells(i).Value 'GOOD
            .txtG2PM.Text = tb.ListColumns("G2_PM").DataBodyRange.Cells(i).Value 'GOOD
            .Show
         
            Exit For
         
         End If
      
      Next
   
   End With

End Sub

 

[sspatriots]

Bingo. Worked perfectly. Just in time for my vacation . When I get back here on the 27th, I'll fill in all the details for the rest of the fields and then I'll need to start on code for an "Update Data" button inside the UserForm that will push the changes back to the table. Thanks so much. Have a great weekend...

BTW: What did I not have in there from the code that I just posted?