CODEWITHSUNDEEP
vbaloopsexceldo-while
user3703785
user3703785

Reputation: 41

using of multiple do while loop in VBA

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.

 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!

Upvotes: 1

Views: 29819

Answers (3)

tk78
tk78

Reputation: 957

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.

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.

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.

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).

Here's the solution using nested do while loops:

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. :-)

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

Upvotes: 0

r_hudson
r_hudson

Reputation: 193

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

Upvotes: 0

MatthewHagemann
MatthewHagemann

Reputation: 1195

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

Upvotes: 1

Related Questions