在 VBA 中使用多个 do while 循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24554982/
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 of multiple do while loop in VBA
提问by user3703785
I would like to ask you how to use multiple do while or if there is another way how to automate this type of calculation. I have worksheet with closing prices of stocks named Closed. On another sheet called Returns i would like to calculate returns. I do not know how many rows and columns will be in Closed. So i wrote macro for first column.
我想问你如何使用多个 do while 或者是否有另一种方法来自动化这种类型的计算。我有一个名为 Closed 的股票收盘价的工作表。在另一张名为 Returns 的工作表上，我想计算回报。我不知道 Closed 中有多少行和列。所以我为第一列写了宏。
Sub CalcReturns()
' CalcReturns
Dim row As Integer
Worksheets("Returns").Activate
row = 3
Do While Cells(row, 1) <> ""
Cells(row, 2).Value = Worksheets("Close").Cells(row, 2).Value / _
Worksheets("Close").Cells(row - 1, 2).Value - 1
row = row + 1
Loop
End Sub
My question is how to add second loop for doing above calculation so far as data are in columns of first row. I tried to study using of loops, but i was able to do just one, not multiple Thanks in advance!
我的问题是如何添加第二个循环来进行上述计算，只要数据位于第一行的列中。我试图研究使用循环，但我只能做一个，而不是多个 提前谢谢！
回答by MatthewHagemann
You can nest do loops:
您可以嵌套执行循环：
Do while firstCondition
Do while secondCondition
Loop
Loop
I'm not sure if this is what you're trying to do, but you can add a nested loop to your code as below:
我不确定这是否是您要执行的操作，但是您可以在代码中添加一个嵌套循环，如下所示：
Sub CalcReturns()
' CalcReturns
Dim row As Integer
Worksheets("Returns").Activate
row = 3
'first loop
Do While Cells(row, 1) <> ""
col = 2
'second loop
Do While Cells(row,col)<>""
Cells(row, col).Value = Worksheets("Close").Cells(row, col).Value / _
Worksheets("Close").Cells(row - 1, col).Value - 1
col = col+1
Loop
row = row + 1
Loop
End Sub
回答by r_hudson
Sub CalcReturns()
' CalcReturns
Dim row As Integer
Worksheets("Returns").Activate
row = 3
do until isempty(cells(row,1))
col = 2
do until isempty(cells(row,col))
Cells(row, col).Value = Worksheets("Close").Cells(row, col).Value / _
Worksheets("Close").Cells(row - 1, col).Value - 1
col = col+1
Loop
row = row + 1
Loop
End Sub
回答by tk78
I haven't fully understand why you need the nested loop but here's an example of a nested do while loop plus an alternative solution.
我还没有完全理解为什么需要嵌套循环，但这里有一个嵌套 do while 循环和替代解决方案的示例。
The code works on the assumption that you have your worksheet name "Close" with the daily closing prices (for stock A and stock B) and the date in the first column.
该代码假设您的工作表名称为“Close”，其中包含每日收盘价（股票 A 和股票 B）以及第一列中的日期。
Stock A Stock B
01.12.2018 1000 345
02.12.2018 1002 350
03.12.2018 1001 351
04.12.2018 1003 352
05.12.2018 1005 348
06.12.2018 1006 349
07.12.2018 1005 352
Plus a second worksheet named "Return" in which you have the same structure for storing the daily return data.
加上第二个名为“Return”的工作表，您可以在其中使用相同的结构来存储每日回报数据。
Stock A Stock B
01.12.2018
02.12.2018 0.20% 1.45%
03.12.2018 -0.10% 0.29%
04.12.2018 0.20% 0.28%
05.12.2018 0.20% -1.14%
06.12.2018 0.10% 0.29%
07.12.2018 -0.10% 0.86%
The code calculates the daily returns for all dates for stock A and then continues with stock B (and additional stocks you might add to the sheet).
该代码计算股票 A 所有日期的每日收益，然后继续计算股票 B（以及您可能添加到工作表中的其他股票）。
Here's the solution using nested do while loops:
这是使用嵌套 do while 循环的解决方案：
Sub CalculateReturns_UsingDoWhile()
Dim wsC As Worksheet, wsR As Worksheet
Dim lngRow As Long, lngCol As Long
Set wsC = Worksheets("Close")
Set wsR = Worksheets("Return")
lngCol = 2
Do While wsC.Cells(2, lngCol) <> vbNullString
lngRow = 3
Do While wsC.Cells(lngRow, lngCol).Value <> vbNullString
wsR.Cells(lngRow, lngCol).Value = wsC.Cells(lngRow, lngCol).Value / wsC.Cells(lngRow - 1, lngCol).Value - 1
lngRow = lngRow + 1
Loop
lngCol = lngCol + 1
Loop
End Sub
Personally, I like the alternative approach of using For loops because there is much less risk of creating infinite loops and I find it easier to read. Both code snippets do the same and so you can choose as per your liking. :-)
就我个人而言，我喜欢使用 For 循环的替代方法，因为创建无限循环的风险要小得多，而且我发现它更易于阅读。两个代码片段的作用相同，因此您可以根据自己的喜好进行选择。:-)
Sub CalculateReturns_UsingFor()
Dim wsC As Worksheet, wsR As Worksheet
Dim lngRow As Long, lngCol As Long
Set wsC = Worksheets("Close")
Set wsR = Worksheets("Return")
For lngCol = 2 To wsC.Range("B1").End(xlToRight).Column
For lngRow = 3 To wsC.Range("A2").End(xlDown).Row
If wsC.Cells(lngRow, lngCol).Value <> vbNullString Then
wsR.Cells(lngRow, lngCol).Value = wsC.Cells(lngRow, lngCol).Value / wsC.Cells(lngRow - 1, lngCol).Value - 1
End If
Next
Next
End Sub
