插入的带有单元格引用的公式无法识别新输入 [英] Inserted formula with cell reference does not recognize new input

问题描述

我发誓昨天才奏效...

I swear this just worked yesterday...

我有一个程序，该程序在某些单元格中创建公式，这些公式取决于后续向其他单元格中输入的数据:

I have a program that creates formulas within certain cells that depend on subsequent data entries into other cells:

Cells(i, 40).Formula = "= (N" & i & ")/AP" & i

因此，可以在事实之后输入和/或更改AP i ，并且无论存在什么值，公式都应为您提供结果.但是，我得到了#Div/0！错误，无论该单元格中的值是多少.当我评估公式(在Excel中)并逐步进行计算时，它显示了这一点(针对一个特定的单元格):

So, APi can be input and/or changed after the fact, and the formula should give you the result for whatever value is there. However, I get a #Div/0! error no matter what value is in that cell. When I evaluate the formula (within Excel) and step through the calculations, it shows this (for one particular cell):

=(N64)/AP64 =(47.35)/AP64 = 47.35/AP64 = 47.35/9 =#DIV/0！

= (N64)/AP64 = (47.35)/AP64 = 47.35/AP64 = 47.35/9 = #DIV/0!

因此，该公式识别出AP64单元格中有一个值，但是没有使用它正确地计算结果.

So, the formula recognizes that there is a value in the cell AP64, but then does not use it to properly calculate the result.

正如我在顶部所说的那样，它按我昨天昨天在测试时所预期的那样工作.现在，我需要其他人开始使用它，当然不需要.相对于这些代码行，我没有对程序进行任何更改.

As I said at the top, this worked as I expected yesterday afternoon when I was testing it. Now that I need other people to start using it, of course, it doesn't. I changed nothing within the program relative to these lines of code.

如果在其他地方回答了这个问题，我深表歉意.-我真的不知道如何对这种类型的问题进行搜索.

I apologize if this is answered elsewhere - I really have no idea how to create a search for this type of problem.

推荐答案

要检查的两件事:

1)所使用的单元格的值被格式化为一个数字(基于两次，我已经获得了#Value的值)，并且

1) Value of the cells used is formatted to a number (i've had #Value based on that a couple times) and

2)验证使用固定引用修改代码是否提供了适当的输出(由于某种原因，"A16"在我显示的一个中未被识别为单元格引用).例如"$ N"和我和"/$ AP"&我

2) verify that modifying the code using fixed references provides the appropriate output ("A16" for some reason wasn't recognized as a cell reference in one I had show up). E.g., "$N" & i & "/$AP" & i

如果两者看起来都不错，您可以尝试以下方法:

If those both look alright, you might try something like:

.Range(.Cells(3,40),.Cells(lr,40)).Formula = "=$N3/$AP3" 'ensure no random spaces

这应该像是一个填充，其中#3与公式的同一行进行迭代.

This should act like a fill-down in which the # 3 is iterated with the same row as the formula.

这篇关于插入的带有单元格引用的公式无法识别新输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！