Data Tables with Goal Seek using VBA

This page demonstrates how to create a one-way and a two way data table using VBA where each scenario requires a goal seek.  Using a goal seek together with VBA code allows you to evaluate different scenarios where the final results require a goal seek function. A project finance model where the debt size is established with a goal seek function driven by the target DSCR is used as an example. The example is used to illustrate basics of creating a VBA program.

The file that is used in explaining how to create the data table with the goal seek is included in the spreadsheet that you can download by pressing the button below.

 

Excel File that Tests Size and Speed of Lookup Function Compared to INDEX and MATCH with Entire Lines Input

 

The first screenshot shows a simple project finance model that is used to demonstrate the VBA code. A simple project finance model is created where the debt size is set from an input DSCR.  In the analysis, a time line, operating cash flow and a debt schedule is established.

 

.

Sub goalseek()
'
' goalseek Macro
'
Range("dif").goalseek Goal:=0, ChangingCell:=Range("debt")
End Sub

.

 

The screenshot below shows how to create a data table with a goal seek function.

 

 

 

.

.

Sub goalseek()
'
' goalseek Macro
'
Range("dif").goalseek Goal:=0, ChangingCell:=Range("debt")
End Sub



.
.
Sub oneway()
For Row = 41 To 47
   Range("dscr") = Cells(Row, 9)
   goalseek
   Cells(Row, 10) = Range("eqirr")
Next Row
End Sub

The screenshot below shows how to create a data table with a goal seek function.

 

Sub twoway()
For Row = Range("startrow") To Range("endrow")
    Range("dscr") = Cells(Row, Range("startcol") - 1)
    For Column = Range("startcol") To Range("endcol")
       Range("tenure") = Cells(Range("startrow") - 1, Column)
       goalseek
       Cells(Row, Column) = Range("eqirr")
     Next Column
Next Row
End Sub

.

.