宏 vba 需要将公式应用于列，直到它在最后一行中具有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14031641/
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
macros vba need to apply formula to column till it has value in the last row
提问by PASUMPON V N
I need to apply "IF "formula in whole C column till has last value in the sheet using VBA .
我需要在整个 C 列中应用“IF”公式，直到使用 VBA 在工作表中具有最后一个值。
but i am getting error 438 if i use the following code . Plz help me
但如果我使用以下代码，我会收到错误 438。请帮助我
Sub test11()
With Sheets("Sheet")
.Range("c1:c" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula = "=IF(B1="",TRIM(A1),TRIM(B1))"
End With
End Sub
采纳答案by bonCodigo
So your sheet name is Sheetor Sheet1? And OP mentioned Sheet name is Sheet2. That removes one error. Secondly, you need to set Dcolumn as .Cells(.Rows.Count,"D").End(xlUp).Row)instead of Acolumn.
所以你的工作表名称是Sheet或Sheet1？OP 提到的工作表名称是Sheet2. 这消除了一个错误。其次，您需要将D列设置为.Cells(.Rows.Count,"D").End(xlUp).Row)而不是A列。
Here is a very ugly code to try out: It takes last used row in count into the Longvariable. Then set the range accordingly for setting up the formulausing AutoFill.
这是一个非常难看的代码来尝试：它将最后使用的行计数放入Long变量中。然后相应地设置范围以设置formulausing AutoFill。
Sub test11()
Dim l As Long
l = Sheets(1).Range("d1:d" & Sheets(1).Cells(Sheets(1).Rows.Count, "D").End(xlUp).Row).Count
With Sheets("Sheet1")
.Range("d1").Formula = "=IF(IsNull(B1),TRIM(A1),TRIM(B1))"
.Range("d1").AutoFill Destination:=Range("d1:d" & l), Type:=xlFillDefault
End With
End Sub
回答by Patrick Honorez
Your logic seems a bit strange, but this works:
你的逻辑似乎有点奇怪，但这有效：
Sub test11()
With Sheets("Sheet1")
.Range(.Range("c1"), .Range("C" & .Rows.Count).End(xlUp)) = "=IF(B1="""",TRIM(A1),TRIM(B1))"
End With
End Sub
You need to double the quotes within quotes in VBA.
Another variant:
您需要在 VBA 中将引号内的引号加倍。
另一种变体：
Sub test12()
With Sheets("Sheet1")
Intersect(.Range("c1").CurrentRegion, .Range("C:C")).Formula = "=IF(B1="""",TRIM(A1),TRIM(B1))"
End With
End Sub
