Vba data table in vba in Excel
The Data table is similar to the Scenario Manager and Goal Seek and does the same prediction function.
A data table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem
There are two types of Data tables; One Variable data table and Two variable data table.
For illustration purpose, we will consider the One-variable data table.
One-variable data table is used while analyzing how different values of one variable in one or more formulas will change the results of those formulas.
The following example illustrates the use of Data table.
Let us build a data table that shows the monthly payments for loan terms ranging from 1 to 6 years.
The Cells C10 to C12 contains the Loan details.
The following code shows how to create a datatable in the range B14:C19
- Sub Datatbl()
- Dim dtsht As Worksheet
- Dim roi, nop, loan As Double
- Set dtsht = Sheets("Q86")
- roi = CDbl(InputBox("Enter Annual rate of interest")) / 100
- dtsht.Range("C10") = roi
- nop = CDbl(InputBox("Enter number of payments in months"))
- dtsht.Range("C11") = nop
- loan = CDbl(InputBox("Enter Loan amount required"))
- dtsht.Range("C12") = loan
- dtsht.Range("C14").FormulaR1C1 = "=PMT(R[-4]C/12,R[-3]C,R[-2]C)"
- dtsht.Range("B14:C19").Select
- Selection.Table ColumnInput:=dtsht.Range("C11")
- End Sub
When executed, the program will demand the user to input Rate of Interest, Payment period and Loan amount.
Once entered, the program will create a datatable in the range B14:C19, corresponding to the values in B14:B19.
The screenshot of the editor is as shown below.
