Automate a function in Excel using VBA

P

ppuvv2

New Member
Joined
May 11, 2018
Messages
1
Hi,

I am new to Excel VBA abd have been stuck in this problem for quite some time now. I am required to
read (n) number of test cases with (n) number of inputs and display the outputs through a button macro in developer ribbon. I'm looking to write a function to accomplish this and any help that I can get in order to complete the task would be greatly appreciated. I want a function so that my code runs for different tyes of inputs and stays generic. Thank you very much.
​

This is the excel file from which I'm taking the different inputs.


Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
FRegular School DistrictAverage
FRegular School DistrictAverage

<colgroup><col style="px"width:" 25pxpx"=""><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Test Cases[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_BirthDate[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_HireDate[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Gender[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_AnnualPlanComp[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_EmployeeClass[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_AnnualPayGrowth[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_MarketPerformance[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_PVD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_NRA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_MBAA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Custom_BCD[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Custom_TermAge[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_RemainingElections[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Pre2011ServiceYears[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjectedServiceYears[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjectedBenefitAmount[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_DROP_LumpSum[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjBuyBackABO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_DateABO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjectedABO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]input_ProjectedAAL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]input_DateAAL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_InvestmentBalanceTBA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_CurrentABO[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]1[/TD]
[TD="align: right"]6/5/1954[/TD]
[TD="align: right"]8/1/2013[/TD]

[TD="align: right"]50224[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7028.58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"][/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]103461.5[/TD]
[TD="align: right"]48696[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]80772.6[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1/4/1983[/TD]
[TD="align: right"]10/1/2012[/TD]

[TD="align: right"]36179.33[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6.33[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4461.18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1/31/2018[/TD]
[TD="align: right"]4461.18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1/1/1900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3340.03[/TD]

</tbody>
ip​



My code on Vba till now i worked on looks like this
Code: 
Sub xp()

    Dim http As Object
    Dim st As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    st =  "Site=SBA_Modeler_V22&Data={'input_BirthDate':'1983-01-04','input_HireDate':'2012-10-01','input_Gender':'F','input_AnnualPlanComp':36179.33,'input_EmployeeClass':'Regular  School  District','input_AnnualPayGrowth':1,'input_MarketPerformance':'Average','input_PVD':38,'input_NRA':63,'input_MBAA':66,'input_Custom_BCD':35,'input_Custom_TermAge':90,'input_RemainingElections':1,'input_Pre2011ServiceYears':0,'input_ProjectedServiceYears':6.33,'input_ProjectedBenefitAmount':0,'input_DROP_LumpSum':4461.18,'input_ProjBuyBackABO':0,'input_DateABO':2018-01-31,'input_ProjectedABO':4461.18,'input_ProjectedAAL':0,'input_DateAAL':1900-01-01,'input_InvestmentBalanceTBA':0,'input_CurrentABO':3340.03}"
   
 
    http.Open "POST", "https://beqlb02.poolt.hewitt.com/dsi0042/calculator", False
    http.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    http.send (st)

    MsgBox (http.responsetext)
    Set JSON = ParseJson("[" + http.responsetext + "]")
    i = 1
    For Each Item In JSON
    Sheets(4).Cells(3, 2).Value = JSON(1).Item("output_Range_Age1")
    Sheets(4).Cells(3, 3).Value = JSON(1).Item("output_Range_Age2")
    Sheets(4).Cells(3, 4).Value = JSON(1).Item("output_Range_Age3")
    Sheets(4).Cells(3, 5).Value = JSON(1).Item("output_Range_Age4")
    Sheets(4).Cells(3, 6).Value = JSON(1).Item("output_Range_Age5")
    Sheets(4).Cells(3, 7).Value = JSON(1).Item("output_Range_Custom")
    Sheets(4).Cells(3, 8).Value = JSON(1).Item("output_Balance_Age1")
    Sheets(4).Cells(3, 9).Value = JSON(1).Item("output_Balance_Age2")
    Sheets(4).Cells(3, 10).Value = JSON(1).Item("output_Balance_Age3")
    Sheets(4).Cells(3, 11).Value = JSON(1).Item("output_Balance_Age4")
    Sheets(4).Cells(3, 12).Value = JSON(1).Item("output_Balance_Age5")
    Sheets(4).Cells(3, 13).Value = JSON(1).Item("output_Balance_Custom")
    Sheets(4).Cells(3, 14).Value = JSON(1).Item("output_Balance_LumpSum")
    Sheets(4).Cells(3, 16).Value = JSON(1).Item("output_CurrentAge")
    Sheets(4).Cells(3, 17).Value = JSON(1).Item("output_MinAgeTerm")
    Sheets(4).Cells(3, 18).Value = JSON(1).Item("output_MaxAgeTerm")
    Sheets(4).Cells(3, 19).Value = JSON(1).Item("output_MinAgeBCD")
    Sheets(4).Cells(3, 20).Value = JSON(1).Item("output_MaxAgeBCD")
    Sheets(4).Cells(3, 22).Value = JSON(1).Item("output_DROP_Question")
    Sheets(4).Cells(3, 23).Value = JSON(1).Item("output_DROP_Years")
    Sheets(4).Cells(3, 24).Value = JSON(1).Item("output_DROP_Start")
    Sheets(4).Cells(3, 25).Value = JSON(1).Item("output_DROP_1stYear")
    Sheets(4).Cells(3, 26).Value = JSON(1).Item("output_DROP_Acumulation")
    Sheets(4).Cells(3, 27).Value = JSON(1).Item("output_DROP_AccumulationAnnuity")
    Sheets(4).Cells(3, 28).Value = JSON(1).Item("output_DROP_TotalAnnuity")
    Sheets(4).Cells(3, 29).Value = JSON(1).Item("output_DROP_COLA")
    Sheets(4).Cells(3, 31).Value = JSON(1).Item("output_BuyBack_Payment")
    Sheets(4).Cells(3, 32).Value = JSON(1).Item("output_BuyBack_IP_AddOn")
    Sheets(4).Cells(3, 33).Value = JSON(1).Item("output_DROP_BuyBack_TotalAnnuity")
    
    i = i + 1
    Next
    
    MsgBox ("complete")
 
End Sub


The following is the excel file from where I am deriving the inputs for different test cases.

Excel 2010
CDEFG
F
Regular School District
Average

<colgroup><col style="px"width:" 25pxpx"=""><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_BirthDate[/TD]
[TD="align: right"]6/5/1954[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Range_Age1[/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_HireDate[/TD]
[TD="align: right"]8/1/2013[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Range_Age2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Gender[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Range_Age3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_AnnualPlanComp[/TD]
[TD="align: right"]50224[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Range_Age4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_EmployeeClass[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Range_Age5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_AnnualPayGrowth[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Range_Custom[/TD]
[TD="align: right"]64[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_MarketPerformance[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Balance_Age1[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Balance_Age2[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_PVD[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Balance_Age3[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_NRA[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Balance_Age4[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_MBAA[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Balance_Age5[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Custom_BCD[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_Balance_Custom[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Custom_TermAge[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]output_Balance_LumpSum[/TD]
[TD="align: right"] $ - [/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_RemainingElections[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_CurrentAge[/TD]
[TD="align: right"]63[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_MinAgeTerm[/TD]
[TD="align: right"]64[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_Pre2011ServiceYears[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_MaxAgeTerm[/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjectedServiceYears[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_MinAgeBCD[/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjectedBenefitAmount[/TD]
[TD="align: right"]7028.58[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_MaxAgeBCD[/TD]
[TD="align: right"]65[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_DROP_LumpSum[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_DROP_Question[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjBuyBackABO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_DROP_Years[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_DateABO[/TD]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_DROP_Start[/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_ProjectedABO[/TD]
[TD="align: right"]103461.51[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_DROP_1stYear[/TD]
[TD="align: right"] [/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]input_ProjectedAAL[/TD]
[TD="align: right"]48696[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_DROP_Accumulation[/TD]
[TD="align: right"] [/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]input_DateAAL[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_DROP_AccumulationAnnuity[/TD]
[TD="align: right"] [/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_InvestmentBalanceTBA[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]output_DROP_TotalAnnuity[/TD]
[TD="align: right"] [/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]input_CurrentABO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00B0F0]#00B0F0[/URL] , align: right"]80772.6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]output_DROP_COLA[/TD]
[TD="align: right"]1.09%[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_BuyBack_PP[/TD]
[TD="align: right"] $ 48,696.00 [/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_BuyBack_Payment[/TD]
[TD="align: right"] $ 48,696.00 [/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC99]#FFCC99[/URL] , align: right"]output_BuyBack_IP_AddOn[/TD]
[TD="align: right"] $ 2,627.08 [/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FDE9D9]#FDE9D9[/URL] , align: right"]output_BuyBack_TotalAnnuity[/TD]
[TD="align: right"] $ 2,627.08 [/TD]

</tbody>
Inputs Outputs​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=Model!B33[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=Model!B34[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=Model!B35[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=Model!B36[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=Model!B37[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]=Model!B38[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G8[/TH]
[TD="align: left"]=Model!B45[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G9[/TH]
[TD="align: left"]=Model!B46[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G10[/TH]
[TD="align: left"]=Model!B47[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G11[/TH]
[TD="align: left"]=Model!B48[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G12[/TH]
[TD="align: left"]=Model!B49[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G13[/TH]
[TD="align: left"]=Model!B50[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G14[/TH]
[TD="align: left"]=Model!B44[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G16[/TH]
[TD="align: left"]=Model!B20[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G17[/TH]
[TD="align: left"]=output_CurrentAge+1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G19[/TH]
[TD="align: left"]=IF(input_Custom_TermAge>input_MBAA,input_Custom_TermAge,MAX(output_MinAgeTerm,input_PVD))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G20[/TH]
[TD="align: left"]=MAX(input_Custom_TermAge,input_MBAA)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G22[/TH]
[TD="align: left"]=Model!G125[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G23[/TH]
[TD="align: left"]=Model!G131[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G24[/TH]
[TD="align: left"]=Model!G133[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G25[/TH]
[TD="align: left"]=IF(output_DROP_Question,input_ProjectedBenefitAmount,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G26[/TH]
[TD="align: left"]=IF(output_DROP_Question,Model!F87,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G27[/TH]
[TD="align: left"]=IF(output_DROP_Question,Model!B86,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G28[/TH]
[TD="align: left"]=IF(output_DROP_Question,Model!B87,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G29[/TH]
[TD="align: left"]=ROUND(output_Global_DROP_COLA/100,4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G31[/TH]
[TD="align: left"]=Model!E100[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G32[/TH]
[TD="align: left"]=Model!E101[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G33[/TH]
[TD="align: left"]=IF(Model!E103<0,0,Model!E103)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G34[/TH]
[TD="align: left"]=IF(output_BuyBack_IP_AddOn=0,Model!B50,Model!B51)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]input_Custom_TermAge[/TH]
[TD="align: left"]='Inputs Outputs'!$D$14[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]input_MBAA[/TH]
[TD="align: left"]='Inputs Outputs'!$D$12[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]input_ProjectedBenefitAmount[/TH]
[TD="align: left"]='Inputs Outputs'!$D$20[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]input_PVD[/TH]
[TD="align: left"]='Inputs Outputs'!$D$10[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]output_BuyBack_IP_AddOn[/TH]
[TD="align: left"]='Inputs Outputs'!$G$33[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]output_CurrentAge[/TH]
[TD="align: left"]='Inputs Outputs'!$G$16[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]output_DROP_Question[/TH]
[TD="align: left"]='Inputs Outputs'!$G$22[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]output_Global_DROP_COLA[/TH]
[TD="align: left"]='Inputs Outputs'!$J$5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]output_MinAgeTerm[/TH]
[TD="align: left"]='Inputs Outputs'!$G$17[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]PVD[/TH]
[TD="align: left"]='Inputs Outputs'!$D$10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You must log in or register to reply here.

Similar threads

Emka
  • Question Question
Web Scrap on Chrome via VBA
Replies
0
Views
104
Emka
Emka
P
  • Solved
How to parse Json data from webpage to excel sheet ?
Replies
4
Views
3K
ppnar007
P
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
400
maiwarits
M
K
  • Solved
Using input excel cell to define sheet name in VBA code
Replies
5
Views
281
mumps
mumps
K
  • Question Question
Excel VBA - Element not found error
Replies
0
Views
137
kyk8898
K

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top