vba 使用VBA将公式插入具有可变行号的单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30419014/
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
Using VBA to insert formula into cell with variable row numbers
提问by Scott
I've got my spreadsheet set up so that when text is entered in column Z, AutoFillmacro is called.
我已经设置好我的电子表格，以便在 column 中输入文本时Z，AutoFill会调用宏。
I need columns AAand ABto be autofilled with a formula up until the last row of text in column Z. I was using the .AutoFill:
我需要列AA并AB用公式自动填充，直到 column 中的最后一行文本Z。我正在使用.AutoFill：
'Range("AA3:AB3").AutoFill Destination:=Range("AA3:AB" & lastRow), Type:=xlFillValues
AA3Starting formula was: =IF(AC3="",IF(Y3="","",AB2),AC3)
AA3起始公式为： =IF(AC3="",IF(Y3="","",AB2),AC3)
AB3Starting formula was: =IF(AD3="",IF(AA3="","",WORKDAY(AA3,(Y3/8))),AD3)
AB3起始公式为： =IF(AD3="",IF(AA3="","",WORKDAY(AA3,(Y3/8))),AD3)
This worked ok but there are times where the cells value are manually entered which takes away the original formula, then messing up the autofill for the rest of the cells. So I took away the auto fill and added a set formula with variable row numbers using ActiveCell.Row.
这工作正常，但有时手动输入单元格值会带走原始公式，然后弄乱其余单元格的自动填充。所以我取消了自动填充，并使用ActiveCell.Row.
This is what I have now:
这就是我现在所拥有的：
Sub AutoFill()
Application.EnableEvents = False
lastRow = Range("Z900").End(xlUp).Row
Range("AA" & (ActiveCell.Row)).Formula = "=IF(AC" & ActiveCell.Row & "="", IF(Y" & ActiveCell.Row & "="","",AB" & ActiveCell.Row - 1 & "),AC" & ActiveCell.Row & ")"
Range("AB" & (ActiveCell.Row)).Formula = "=IF(AD" & ActiveCell.Row & "="",IF(AA" & ActiveCell.Row & "="","",WORKDAY(AA" & ActiveCell.Row & ", (Y" & ActiveCell.Row & "/8))),AD" & ActiveCell.Row & ")"
Application.EnableEvents = True
End Sub
Now I get a runtime error and the first "Range("AA"..."is highlighted. What am I doing wrong?
现在我收到一个运行时错误，第一个"Range("AA"..."被突出显示。我究竟做错了什么？
I am using Excel 2010.
我正在使用 Excel 2010。
回答by Slai
You can simplify it to
您可以将其简化为
Range("AA3:AA" & lastRow).Formula = "=IF(AC3="""",IF(Y3="""","""",AB2),AC3)"
Range("AB3:AB" & lastRow).Formula = "=IF(AD3="""",IF(AA3="""","""",WORKDAY(AA3,(Y3/8))),AD3)"
The row numbers that don't have $will be auto incremented.
没有的行号$将自动递增。
回答by Scott
All quotations with blanks ""were changed to """". Quotations within quotations. This fixed the runtime error to allow the formula to work.
所有带空格的引文""都改为"""". 引文中的引文。这修复了运行时错误以允许公式工作。
Range("AA" & (ActiveCell.Row)).Formula = "=IF(AC" & ActiveCell.Row & "="""", IF(Y" & ActiveCell.Row & "="""","""",AB" & ActiveCell.Row - 1 & "),AC" & ActiveCell.Row & ")"
Range("AB" & (ActiveCell.Row)).Formula = "=IF(AD" & ActiveCell.Row & "="""",IF(AA" & ActiveCell.Row & "="""","""",WORKDAY(AA" & ActiveCell.Row & ", (Y" & ActiveCell.Row & "/8))),AD" & ActiveCell.Row & ")"
