# Thread: using of multiple do while loop?

1. Registered User
Join Date
May 2014
Posts
10

## Unanswered: using of multiple do while loop?

Hi,
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!

2. Registered User
Join Date
Apr 2010
Location
New York USA
Posts
2
How are ya mato61 . . .

You can use the End property to obtain your last row & column values. I didn't know if your row count would be even or not, so I parse across the columns holding the column with the highest row count. I've highlited the end properties so there easy to find in the code. Once the end properties are obtained a nested for next loop easily takes care of the rest.

Try the following (not sure of your sheet names so check them in purple). Work on a copy of the workbook until its wright. To read about the end property see End(XlUp) For Last Used Row in Column

Any questions just let me know.

Sub CalcReturns()
Dim Clo As Worksheet, Ret As Worksheet
Dim Dat1, Dat2
Dim rowEnd As Long, colEnd As Long
Dim row As Long, col As Long

Set Clo = ThisWorkbook.Sheets("Closed")
Set Ret = ThisWorkbook.Sheets("Returns")
'get last column
colEnd = Clo.Cells(3, Clo.Columns.Count).End(xlToLeft).Column

[COLOR="rgb(46, 139, 87)"]'get rowEnd for column with highest row[/COLOR]
For col = 2 To colEnd
row = Clo.Cells(1000, col).End(xlUp).row
If row > rowEnd Then rowEnd = row
Next

'main loop
For col = 2 To colEnd
For row = 3 To rowEnd
Dat1 = Clo.Cells(row, col).Value
Dat2 = Clo.Cells(row - 1, col).Value

If Not IsEmpty(Dat1) And Not IsEmpty(Dat2) Then
Ret.Cells(row, col).Value = Clo.Cells(row, col).Value / _
Clo.Cells(row - 1, col).Value
End If
Next
Next

Ret.Activate
Set Ret = Nothing
Set Clo = Nothing

End Sub

Thats it ... cheers! ...

3. Registered User
Join Date
Apr 2014
Location
Kentucky
Posts
614
Code:
```Dim row As Long   '(use LONG not integer, you will run out of memory.  # rows excedes integer limit)
Dim col As Long

Do While Cells(row, 1) <> ""
For col = 2 To 4
Cells(row, c).Value = Worksheets("Close").Cells(row, 2).Value / _
Worksheets("Close").Cells(row - 1, c).Value - 1
Next
row = row + 1
Loop
Next```

4. Registered User
Join Date
May 2014
Posts
10
thank you! that is great!