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,


  • 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
      .... "=[COLOR=#0000FF]RC[1][/COLOR]&TEXT(ROW(),""0000"")"

    • To
      ".... =[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,


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!