I have a spreadsheet with say 6 columns. off to the right, I'm creating a formula in say cell m2, then copying that formula down until the end of the data. the formula in m2 would be something like a concatenate formula that uses two columns, say column b and column c. currently, my formula in m2 looks something like this:

ActiveCell.FormulaR1C1 = "=CONCATENATE(""I"",RC[-9])"

this example uses only one column and a hardcoded "I", but you get the picture.

my question is this. is there a way I can avoid using the RC[-x] format? I have column headings. is there a way the formula can simply look for the column heading to determine which cell to use at the top of that column and then copy it down?

don't worry about the copy down part. I can handle that. I'm simply looking for a way to get that first formula in cell m2 to use something other than those RC[-x] format and something more "permanent".

hopefully, this makes sense?

best regards,

david