Excel VBA公式中的奇怪行为 [英] Strange Behavior in Excel VBA Formula

问题描述

这是：

 
 Private Sub fillLicenseFormulas（）
 
 Dim LastRow As Long 
 LastRow = ActiveSheet.UsedRange.Rows.Count 
 Dim gRange As String 
 
 gRange =" G3：G" &安培; LastRow& "" 
范围（" g3"）。公式=" = IF（$ G $ 3  - （$ F $ 3 + $ E $ 3）< 0,0，$ G $ 3  - （$ F $ 3 + $ E $ 3 ））" 
 
设置SourceRange = ActiveSheet.Range（" g3：g3"）
设置fillRange = ActiveSheet.Range（gRange）
 SourceRange.AutoFill目的地：= fillRange，类型：= xlFillDefault 
 
 

奇怪的是，在Excel中，这就是结果：  = IF（$ J $ 6 - （$ I $ 6 + $ H $ 6）< 0,0，$ J $ 6 - （$ I $ 6 + $ H $ 6））

（尽管"G"引用，该公式实际上是在J）

我从一个前任那里继承了这个，他从另一个Excel工作表中进行了相当多的复制/操作。 我怎样才能确保公式来自最终专栏地址？

谢谢。

---

A。 Wolf

解决方案

代码对我来说有点奇怪，你真的想在多个单元格中放入相同的公式吗？由于公式中的绝对引用，这可能会发生，也许它们应该是相对的引用。使用usedrange
查找最后一行可能不可靠，我会像下面这样做。

说完你的代码应该填充Col G并为我做。我们这里有所有代码吗？

[代码]

Dim LastRow As Long，SourceRange作为Range，fillrange As Range

Dim gRange As String

LastRow = Cells.Find（What：=" *"，After：= [A1]，_

SearchOrder：= xlByRows，_

SearchDirection：= xlPrevious）.Row

gRange =" G3：G" &安培; LastRow& "< b $ b范围（"g3"）。公式="= IF（G3-（F3 + E3）< 0,0，G3-（F3 + E3））"。

设置SourceRange = ActiveSheet.Range（" g3：g3"）

设置fillrange = ActiveSheet.Range（gRange）

SourceRange.AutoFill目的地：= fillrange，类型：= xlFillDefault

[/ Code]

HERE IT IS:

Private Sub fillLicenseFormulas()

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
Dim gRange As String

gRange = "G3:G" & LastRow & ""
Range("g3").Formula = "=IF($G$3-($F$3+$E$3)<0,0,$G$3-($F$3+$E$3))"

Set SourceRange = ActiveSheet.Range("g3:g3")
Set fillRange = ActiveSheet.Range(gRange)
SourceRange.AutoFill Destination:=fillRange, Type:=xlFillDefault

Oddly, in Excel, this is the result:  =IF($J$6-($I$6+$H$6)<0,0,$J$6-($I$6+$H$6))

(Despite the "G" reference, the formula is actually in J)

I inherited this from a predecessor who was doing quite a bit of copy/manipulation from another Excel worksheet.  How can I insure that the formula pulls from the FINAL COLUMN ADDRESSES?

Thanks.

---

A. Wolf

解决方案

Hi,

The code seems a bit odd to me, are you really wanting to put the same formula in multiple cells? That's what's going to happen because of the absolute references in the formula, maybe they should be relative references. Also finding the last row using usedrange can be unreliable, I'd do it like below.

Having said that your code should populate Col G and does for me. Do we have all the code here?

[Code]

Dim LastRow As Long, SourceRange As Range, fillrange As Range
Dim gRange As String
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
gRange = "G3:G" & LastRow & ""
Range("g3").Formula = "=IF(G3-(F3+E3)<0,0,G3-(F3+E3))"

Set SourceRange = ActiveSheet.Range("g3:g3")
Set fillrange = ActiveSheet.Range(gRange)
SourceRange.AutoFill Destination:=fillrange, Type:=xlFillDefault

[/Code]

这篇关于Excel VBA公式中的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！