Microsoft KB Archive/201428

= XL2000: Macro That Inserts Formula Returns #NAME? =

Article ID: 201428

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q201428





SYMPTOMS
When you run a macro that inserts a formula into a cell, the formula returns the #NAME? error value.



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



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Additional query words: XL2000 TFE Lotus 123

Keywords: kbbug kbpending KB201428

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.