Hi all,
I've recently inherited a Financial Forecasting Model which was created rather a long time ago. The model was using lots of pre-2007 Excel formulas, which I've tried to get rid of.
https://drive.google.com/file/d/0B__bxAaMPpZZTEFrMEQ2Njl2eTA/view?usp=sharing
In addition to this, it also has some VBA code, which computes multiple iterations using a Do and Loop functions. This seems to run quite slowly, especially, as the model was originally intended to run up to 2000 iterations. Hence the 2000 rows on the "Iteration Results" Worksheet in the linked workbook.
Below is the code, which I've already cleaned up a little.
I have two questions:
With respect to the 2nd question, If you specify 10, 20 or 30 iterations etc. the code will produce 7, 17 or 27 rows worth of data. I don't think this can have been intentional?
I've worked out a cludgy fix, which was to change
to
As you can probably tell, I'm rather new to VBA, so any help would be greatly appreciated.
I've recently inherited a Financial Forecasting Model which was created rather a long time ago. The model was using lots of pre-2007 Excel formulas, which I've tried to get rid of.
https://drive.google.com/file/d/0B__bxAaMPpZZTEFrMEQ2Njl2eTA/view?usp=sharing
In addition to this, it also has some VBA code, which computes multiple iterations using a Do and Loop functions. This seems to run quite slowly, especially, as the model was originally intended to run up to 2000 iterations. Hence the 2000 rows on the "Iteration Results" Worksheet in the linked workbook.
Below is the code, which I've already cleaned up a little.
I have two questions:
- How can I make it run faster/is there a less intensive way to achieve the same result?
- I can't figure out why the number of iterated rows is always 3 less than the number of iterations declared?
With respect to the 2nd question, If you specify 10, 20 or 30 iterations etc. the code will produce 7, 17 or 27 rows worth of data. I don't think this can have been intentional?
I've worked out a cludgy fix, which was to change
Code:
Perc = 100 * (Row / (It + 10))
Code:
Perc = 100 * (Row / (It + 13))
As you can probably tell, I'm rather new to VBA, so any help would be greatly appreciated.
Code:
Sub cmd_RunStats_Click()
'
' Run Stats Macro
' Clear Data Table
Range("U14:AS2013").ClearContents
Range("A1").Select
' Set Variables
Dim Row As Integer
Dim Col As Integer
Dim Perc As Integer
Dim It As Integer
Dim TenPerRow As Integer
Dim FifPerRow As Integer
Dim NinPerRow As Integer
Row = 13
Col = 21
Perc = 0
' Input Number of Iterations from Cell "T5"
It = Cells(5, 20).Value
' Set Row for Percentage Results
TenPerRow = It * 0.1
FifPerRow = It * 0.5
NinPerRow = It * 0.9
' Data Collection Loop for number of iterations
Do While Row < (It + 13)
'Increment current storage Row and recalculate results
Row = Row + 1
Calculate
'Put iteration data into current storage row
For myCol = 21 To 45
Cells(Row, myCol).Value = Cells(7, myCol).Value
Next myCol
'Calculate and display the percentage progress in Cell "U5"
'Perc = 100 * (Row / (It + 10)) ' ORIGINAL CODE
Perc = 100 * (Row / (It + 13)) 'CLUDGY FIX
Cells(5, 21).Value = Perc
Loop
' End of Loop
End Sub