Looping through each cell in table by column then row: retrieving values

[peterskrystalk]

I have an excel workbook with a list of contracts to be submitted. The information in the workbook is used to create a email (form) where the information is filled in and files are attached. Originally the code:

For Each rCol In tbl.DataBodyRange
'***************************************************
' Contracting Entity - place the value of the cell in the variable
'***************************************************
If rCol.Column = 1 Then
Entity = rCol.Text
'***************************************************
' Vendor Name - place the value of the cell in this column &row in the variable
'***************************************************
ElseIf rCol.Column = 2 Then
Vndr = rCol.Text

and so on...would populate a variable base on the number of the column. Since then I have had to rearrange and add more columns - and edit my code. How can this be coded so I am not dependent on the location of the column but just the name.. So that if any future addition or rearrangement happens, I don't have to modify all my code, I can just add the reference to the new column.

thanks, peterskrystalk

 

[andrewkard]

Hello, you can add the reference to the new column in this code like:
ElseIf rCol.Column = 3 Then ....
ElseIf rCol.Column = 4 Then ....
ElseIf rCol.Column = 5 Then ....

where 3 is according to column C, 4 to D, 5 to E and go on.

 

[peterskrystalk]

Thanks, however that is what I am currently doing. When the user makes changes to the arrangement of the columns the code has to be edited. I am using the table feature in Excel 2010 and was hoping I could have this coded with more flexibility, so I don't have to make changes to the code when any column is rearranged.

peterskrystalk

--------------------------------------------------------------------------------------------------------

Hello, you can add the reference to the new column in this code like:
ElseIf rCol.Column = 3 Then ....
ElseIf rCol.Column = 4 Then ....
ElseIf rCol.Column = 5 Then ....

where 3 is according to column C, 4 to D, 5 to E and go on.

 

[peterskrystalk]

Found a solution! Instead of using :

For Each rCol In tbl.DataBodyRange
'***************************************************
' Contracting Entity - place the value of the cell in the variable
'***************************************************
If rCol.Column = 1 Then
Entity = rCol.Text
'***************************************************
' Vendor Name - place the value of the cell in this column &row in the variable
'***************************************************
ElseIf rCol.Column = 2 Then
Vndr = rCol.Text

changed it to:

'***************************************************
' Contracting Entity
'***************************************************'
If tbl.ListColumns("Contracting Entity").Index = rCol.Column Then
Entity = rCol.Text
'***************************************************
' Vendor Name
'***************************************************
ElseIf tbl.ListColumns("Vendor Name").Index = rCol.Column Then
Vndr = rCol.Text

This way if the column gets moved I don't have to change my code. Now I just need to make sure they don't change the column header.

Thanks, peterskrystalk