Dynamic Array: Multidimensional

  • Hi guys. My sub below works however right now I have the output go to a sheet. However, I want the output to go to an array that I can send to a window or a window with a chart. Anyways, I'm not that knowledgable in arrays and need everyones help.


  • Re: Dynamic Array: Multidimensional


    try ...


    Public Sub realScen()

    Dim intPrice As Double
    Dim EBITDA As Double
    Dim ROR As Double
    Dim Price As Double
    Dim newprice As Double
    Dim ArrOutput(13,2) ' resize if the 'for' loop changes

    intPrice = Sheets(1).Range("Price").Value
    Price = intPrice

    Dim j As Integer
    j = 1
    For i = 0.875 To 1.15 Step 0.02
    newprice = Price * i

    Sheets(1).Range("Price").Value = newprice
    EBITDA = Sheets(1).Range("EBITDA").Value
    ROR = Sheets(1).Range("ROR").Value

    Sheets(2).Cells(10 + j, 1).Value = newprice
    ArrOutput(j-1,0) = newprice
    Sheets(2).Cells(10 + j, 2).Value = EBITDA
    ArrOutput(j-1, 1) = newprice
    Sheets(2).Cells(10 + j, 3).Value = ROR
    ArrOutput(j-1, 2) = newprice
    j = j + 1

    Next i
    'ArrOutput now has the values stored, locally to the procedure.
    ' loop through the array to access them or
    ' set a range of the same sixe as the array = ArrOutput() to populate the range
    ' with the values.
    End Sub

    Kieran

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!