Alternative to Offset in a table

[Factotum]

Would anyone know an alternative to Offset? I'm using a table, so my current code is:

For each c in Range("Table1[Entity Code]")
    If c. value="x" Then
          c.offset(0,-16).value="y"
    End If
Next

I'm hoping to do something more along the lines of:

For each c in Range("Table1[Entity Code]")
    If c. value="x" Then
          c.Range("Table1[Product Code]").value="y"
    End If
Next

Obviously, the above won't work, but hopefully illustrates what I'm going for. Essentially the same thing as an offset, just using column names instead of counting the number of columns.

The goal is to make it so the macro still works if the column order ever changes.

 

[pbornemeier]

I had a similar problem when sources not under my control that were providing datasets kept modifying the column layout.
I added a pre-processing step to scan the column heads and assign column numbers to the variables I created for each column. If a particular column was not found due to spelling changes or obstinacy, the pre-processing step would allow me to compensate.

 

[louisH]

Hi,

You can get to the matching cell of another column via its row and column numbers.

You'll get the column number using : Range("Table1[Product Code]").Column
And the row number using c.Row

Then just use the good old Cells(row, column) to access it.

For example :

For each c in Range("Table1[Entity Code]")
If c. value="x" Then
Cells(c.Row, Range("Table1[Product Code]").Column).value="y"
End If
Next

Hope it helps

 

[Factotum]

Thank you Phil and Louis!

Phil, I did something similar to what you're suggesting for a different project - I'm not sure why it didn't cross my mind this time. Thanks for the suggestion.

Louis - I think you nailed it on the head. I'll test it out and report back, but I'm pretty sure this is exactly the trick I was looking for. Thanks a million!

 

[Factotum]

Louis, it worked beautifully! Thank you so much! This solves the problem if the original report output gets shuffled around. As long as the column headers don't change I'll be fine. I think it also makes the code much more readable. Rather than having to count rows and columns, I can just look to the header name since the row stays constant. Thanks again!