Smart way to reference cells from certain column on same row

[Jacc]

I have a "current row" and I want to do calculations and manipulations of values from certain columns on that same row.
I either use Excel Table feature or name columns. I use the intersect command in VBA to reference cells on the same row from different columns. While this works and is robust it is also cumbersome and makes for a messy code.
I'm about as comfortable using offset and a fixed number as I am playing Russian roulette because I know columns will be rearranged/added/deleted every now and then.

In this thread I use the space operator in a formula (sorry you have to download the workbook). https://www.excelforum.com/excel-gen...x-matches.html

Is there a way to use the space operator in VBA via the evaluate command or something?
Any hint, clue, tip or experience about this type of problem is welcome.

PS. Yes there is a reason (multiple actually) that I don't just use formulas in the worksheet.

[TMS]

I'd be inclined to use Application.Match to determine the column and then use something like Cells(c.Row, lColumn) to refer to the cell, where lColumn is the column number (from the Match). You would only need to do the Match at the beginning of the process (before the loop starts).

Please Login or Register  to view this content.

[Jacc]

To be honest I already use the named columns to get a difference in column numbers and then use that number in offset.
Using cells is a bit slicker, I'll try that, thanks!