VBA Formula Change RC (Column) reference to (Column) NamedRange

Hello,

• I have several named ranges for columns, for example: 'R_ScoreDelivery' for column BG
• Via macro I am adding several calculations that take place within a row, for example: .... IF(OR(RC[-14] ... where in this instance RC[-14] points to column 'R_ScoreDelivery'

Is there a way to move away from the 'C[-14]' and incorporate the named range 'R_ScoreDelivery' instead so as to having a more flexible macro if columns were to be added/removed in-between at some point?

Thank you for your advice,

Stefan

Thank you jolivanes.

I remember having tried that before but could not remember the outcome, so repeated the task with one of the simpler formulas.

The following takes the date entered in a cell in column B (R_Date), uses its Excel Date value and adds the row number as 4 digit code - whereas 02/02/18 in B10 becomes 431330010 in A10 (column A (R_RecordNo)) when it functions as desired.

• I changed
  
  Code

  .... "=[COLOR=#0000FF]RC[1][/COLOR]&TEXT(ROW(),""0000"")"

  
  

• To
  
  Code

  ".... =[COLOR=#0000FF]Range(""R_Date"")[/COLOR]&TEXT(ROW(),""0000"")"

  
  

• The result changes from the desired 431330010 with the formula in A10 being =B10&TEXT(ROW(),"0000")
• The result is now a #Name? error with the formula in A10 being =range("R_Date")&TEXT(ROW(),"0000")

I suppose if nothing else it requires not only the column reference, but also a row reference; the formula, when placed via macro into the cell, does not know what to do with the 'Range' bit - and I need to have the formula placed in the cell, not just the resulting value in case that matters. - Variations of placing a 'R' (Row) reference for the RC (R1C1) reference style I attempted failed.

Thank you,

Stefan