1

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!

3 Answers 3

1

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
Sign up to request clarification or add additional context in comments.

Comments

0
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

1 Comment

Avoid selections and activations
0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.