Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Posts
    10

    Question 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. #2
    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. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    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. #4
    Join Date
    May 2014
    Posts
    10
    thank you! that is great!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •