XL2000: Macro That Inserts Formula Returns #NAME? (201428)

CAUSE

This problem occurs when you do all of the following:

You programmatically insert a formula into a cell.

-and-
The formula refers to cells using both a named cell reference and an A1 style reference, where the named cell reference begins with a letter followed by a number, for example: P4D or S1Test.

-and-
The formula uses the Lotus 1-2-3 formula entry method of using an absolute reference with the named cell reference.

For example, the following macro causes the #NAME? error:

Sub makeFormula()
     With ActiveSheet
        .TransitionFormEntry = True
        .Range("A1").Value = 1
        .Range("A2").Value = 2
        .Range("A2").Name = "A2Range"
        .Range("A3").Formula = "=sum(A1,$A2Range)"
        .TransitionFormEntry = False
   End With
End Sub

WORKAROUND

To work around this behavior, use either A1 style references or named references exclusively.

The following examples illustrate how to insert a formula into a cell without receiving a #NAME? error value.

Example Using A1 Style Reference

Sub makeFormulaA1Ref()
   With ActiveSheet
      .TransitionFormEntry = True
      .Range("A1").Value = 1
      .Range("A2").Value = 2
      .Range("A3").Formula = "=sum(A1,A2)"
      .TransitionFormEntry = False
   End With
End Sub

Example Using a Named Reference

Sub makeFormulaNamedRef()
    With ActiveSheet
        .TransitionFormEntry = True
        .Range("A1").Value = 1
        .Range("A1").Name = "P3D"
        .Range("A2").Value = 2
        .Range("A2").Name = "P4D"
        .Range("A3").Formula = "=sum($P3d,$P4D)"
        .TransitionFormEntry = False
    End With
End Sub