vba 运行时错误：1004 参考因目标搜索而无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16903899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Runtime Error: 1004 Reference is Not Valid resulting from Goal Seek
提问by TheTreeMan
First of all, here is the Excel chart in question, before running the code:
首先，在运行代码之前，这是有问题的 Excel 图表：
and after running the code:
并在运行代码后：
Here is the code in question:
这是有问题的代码：
Option Explicit
Dim Counter As Long
Dim DeSimpleFinal As Double
Sub SimpleDeCalculationNEW()
Counter = 13
Range("C1") = "CFL Calculated"
Range("D1") = "Residual Squared"
Range("E1") = "De value"
Range("F1") = 0.2
'Inserting Equations
Range("C2") = "=((2 * $H)/ $H) * SQR(($F * A2) / PI())"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C" & Counter + 1), Type:=xlFillDefault
Range("D2") = "=((ABS(B2-C2))^2)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & Counter + 1), Type:=xlFillDefault
'Summing up the residuals squared
Range("D" & Counter + 2) = "=Sum(D2, D" & Counter + 1 & ")"
Columns("A:Z").EntireColumn.EntireColumn.AutoFit
'Goal Seek
Range("D" & Counter + 2).GoalSeek Goal:=0, ChangingCell:=Range("F1")
DeSimpleFinal = Range("F1")
'Range("XFD1048523") = DeSimpleFinal
MsgBox ("The Final Value for DeSimple is: " & DeSimpleFinal)
End Sub
I keep getting this error, Runtime Error: 1004 Reference is Not Valid, and all of those name errors on the worksheet. Does anybody know how I can fix this? I'm trying to get the sum of the residuals squared as close to zero as possible, in order to get the best possible fit for my data. I do this by using Goal Seek, which is varying the value of De, given in F1, in order to minimize the sum of the residuals squared, given in D15.
我不断收到此错误，运行时错误：1004 引用无效，以及工作表上的所有这些名称错误。有谁知道我该如何解决这个问题？我试图使残差平方和尽可能接近于零，以便尽可能适合我的数据。我通过使用 Goal Seek 来做到这一点，它改变了 F1 中给出的 De 值，以最小化 D15 中给出的残差平方和。
I've gotten this method to work before without issue, and I'm not really sure why it's failing here. Any advice would be amazing!
我之前已经使用这种方法没有问题，我不确定为什么它在这里失败。任何建议都会很棒！
回答by Kazimierz Jawor
I'm not sure if this fix all the problems but change your function line into the following:
我不确定这是否能解决所有问题，但将您的功能行更改为以下内容：
Range("C2").Formula = "=((2 * $H)/ $H) * SQRT(($F * A2) / PI())"
(SQRTinstead of SQR)
(SQRT而不是SQR)
Additional tip, sometimes it could be better to name correctly object properties, in your situation I would suggest to use .Formulawhen inserting function into range, like:
附加提示，有时最好正确命名对象属性，在您的情况下，我建议.Formula在将函数插入范围时使用，例如：
Range("C2").Formula = "=((2 * $H)/ $H) * SQRT(($F * A2) / PI())"
Range("D2").Formula = "=((ABS(B2-C2))^2)"
Range("D" & Counter + 2).Formula = "=Sum(D2, D" & Counter + 1 & ")"
I know it's working usually what you have but my suggestion is a good habit which I stick to :)
我知道它通常可以正常工作，但我的建议是我坚持的好习惯:)
