vba 停止 excel do-loop 直到
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14452446/
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
stop excel do-loop until
提问by user1449596
I have two columns A and B with numbers as values.
我有两列 A 和 B 以数字作为值。
- In C1 I want
=A1 + B1 - In C2 I want
=A2 + B2
- 在 C1 我想要
=A1 + B1 - 在 C2 我想要
=A2 + B2
and so on. I have written the following VBA code - while it works it adds "0" after the end of the last row in range.
等等。我编写了以下 VBA 代码 - 虽然它可以工作，但它会在范围内的最后一行末尾添加“0”。
Let's assume my last row is A10. It adds "0" in C11 when I run the code. How do I prevent this?
假设我的最后一行是 A10。当我运行代码时，它在 C11 中添加了“0”。我如何防止这种情况？
Sub macro()
Dim R As Long
R = 1
Do
Cells(R, "C").Select
R = R + 1
ActiveCell.Formula = "=sum(" & ActiveCell.Offset(0, -2) & "," &
ActiveCell.Offset(0, -1) & ")"
Loop Until IsEmpty(ActiveCell.Offset(0, -2))
End Sub
回答by Peter L.
Just replace your Until condition to the following string:
只需将您的直到条件替换为以下字符串：
Loop Until IsEmpty(ActiveCell.Offset(1, -2))
Loop Until IsEmpty(ActiveCell.Offset(1, -2))
That will check the right cell for being empty. The rest of your code should remain intact.
这将检查正确的单元格是否为空。其余代码应保持不变。
回答by bonCodigo
Take a look at Do Untiland Do Whileand While.
If you really want to iterate over cells you may go ahead. But here a method using Arrays, this will by all means reduces any performance drops that you would get looping over cells...
如果你真的想迭代单元格，你可以继续。但是这里使用了一种方法Arrays，这将通过各种方式减少您在单元格上循环的任何性能下降......
Option Explicit
Sub AddToRigh()
Dim i As Integer
Dim vArr As Variant
Dim LastRow As Long
'--assume you are working on Sheet 1
LastRow = Sheets(1).Cells(Rows.Count, Range("A1").Column).End(xlUp).Row
ReDim vArr(1 To LastRow)
For i = LBound(vArr) To UBound(vArr)
vArr(i) = "=Sum(RC[-2]:RC[-1])"
Next i
'--output this entire array with formulas into column C
Sheets(1).Range("C1").Resize(UBound(vArr)) = Application.Transpose(vArr)
End Sub
Output:
输出：
回答by Koenyn
I'm by no means an expert in vba, but you could do this:
我绝不是 vba 专家，但你可以这样做：
Sub macro()
Dim R As Long
R = 1
Do While Not IsEmpty(ActiveCell.Offset(0, -2))
Cells(R, "C").Select
R = R + 1
ActiveCell.Formula = "=sum(" & ActiveCell.Offset(0, -2) & "," &
ActiveCell.Offset(0, -1) & ")"
Loop
End Sub
回答by mkingston
I thought I'd recommend a slightly different course of action, just to give you ideas :):
我想我会推荐一个稍微不同的行动方案，只是为了给你一些想法:)：
Sub macro()
Dim found As Range
Set found = Range("A:A").Find("*", after:=Range("A1"), searchdirection:=xlPrevious)
If Not found Is Nothing Then
Range(Range("A1"), found).Offset(0, 2).FormulaR1C1 = "=RC[-2]+RC[-1]"
End If
End Sub
