Using a range variable inside a VBA formula. RRS feed

  • Question

  • Hi,

    Based on the below code, I'm receiving a #Name error as a value for this formula. When doing a formula evaluation, it's coming directly from the "Range" and first "Cell" portions of the formula. Essentially, I have a variable "DNCN" that changes based upon dependent criteria, and I need to allow for this possibility of change within the formula.

    Range("BM6", Cells(LRC2, LDCol)).Formula = "=INDEX('Sku Detail'!$15:$10000,MATCH($J6,'Sku Detail'!$H:$H,0),MATCH(BM$5, Range(Cells(15," & DNCN & "), Cells(15, 400)),0))"

    "Range(Cells" - both these are showing as #Name when the formula is evaluated. Would you happen to know a solution to incorporating my variable into my formula structure?

    Thanks in advance!

    Saturday, August 24, 2013 6:37 PM

All replies

  • You can't use the VBA keyword Range within a worksheet formula. Try this version:

        Range("BM6", Cells(LRC2, LDCol)).FormulaR1C1 = _
            "=INDEX('Sku Detail'!R15:R10000,MATCH(RC10," & _
            "'Sku Detail'!C8:C8,0),MATCH(R5C, R15" & DNCN & ":R15C400,0))"

    Regards, Hans Vogelaar (

    Saturday, August 24, 2013 7:09 PM