VBA macro to run a model

[MCbaffinbay]

Hi. I am seeing if anyone knows how to create a VBA macro to run a data model I have built using unique rows of variables (input) and recording the output, for 100 different scenarios. I have about 5 unique variables that result in an IRR calculation in my model. Can I automate this so I don’t need to run the model manually 100 different times?

 

[offthelip]

welcome to the forum, I have done this many times, You haven't said where your input and output of your model are, so I have assume the inputs are in range A2 to A6 and the results of your model are in range D2 to D6. Also you haven't specified what sort of values you might want to put into the inpuits. Thus in my code I have used various techniques to show you want can be done for the input, i.e. reading from an array, incrementing using a fraction step , etc. The results are written to the worksheet called "Resutls" so you will need to create this. For my test I just put simpl equations in D2 to D6 such as sum ,average, stdev, sumif
It generate over 3000 lines of results in a few seconds

Sub test()
 Valr = Array(98, 110, 115, 213, 400)
With Worksheets("Results")
 Dim outarr(1 To 1, 1 To 10)  ' scale this large enough to include all the inputs and all the results
 indi = 2
 For i = -1 To 1 Step 0.2
   Range("B2") = i
   outarr(1, 1) = i
  For j = 0 To 4
     Range("B3") = Valr(j)
     outarr(1, 2) = Valr(j)
     For k = 50 To 54 Step 1
       Range("B4") = k
       outarr(1, 3) = k
       For m = 1 To 3
         Range("B5") = m
         outarr(1, 4) = m
          For n = -1 To -5 Step -1
           Range("B6") = n
           outarr(1, 5) = m
            res = Range("d2:d6") ' pick up results
            ' copy to results sheet
            For p = 1 To 5
            outarr(1, 5 + p) = res(p, 1)
            Next p
            .Range(.Cells(indi, 1), .Cells(indi, 10)) = outarr
            indi = indi + 1
           Next n
        Next m
      Next k
   Next j
 Next i
 End With
End Sub

 

[MCbaffinbay]

Thanks for your quick suggestion/help. I will play around to see if I can get this to work.

For clarification, we can assume inputs for various scenarios to be in in rows (A2:E2), then next scenario inputs below in rows A3:E3…and so forth for 100 different scenarios. Inputs variables are buildout cost, rents, revenue, cost/expense.

The model itself is a cash flow proforma model with the output being an IRR %. I want to capture automatically different IRR based on each input assumption from the 100 different scenarios. This output can be in a different worksheet (results) or new column next to the input variables. Thanks.

 

[MCbaffinbay]

Still struggling with this one. Here are some clarifications. INPUT: I have a data sheet with 100 rows (scenarios) consisting of one variable input (rental income). DATA MODEL: I have a separate workbook that consists of a working model. Once I input the variable from INPUT sheet, will produce an output (IRR%). I want a VBA macro to input each input from each scenario and record the output for each SCENARIO, and continue through automatically for each 100 rows of scenarios and record it's associated output IRR% (either in a new sheet or next to original variable inputs). Is there a VBA macro that can help with this? (screenshot setup attached as an example)

 

[offthelip]

It is not clear to me what you want. Can you give more details e.g which cells on which sheets you want to read and copy to where? Do you need to write into any cells to trigger the model?