Summing cells from different worksheets within a formula in vba
Hi I have been trying to write a code that sets a function equal to 0 based on a variable input. However the function is a summation of terms that each involve the variable input, so solver does not work. I have started to write some of the code yet I have to sum up cells within the function code, and it keeps producing an error. I have very limited coding knowledge and I have no idea what is wrong with it. Here is the code any help would be greatly appreciated.
I saw what i did on that, but I want it to calculate the break even interest rate and put that value in cell al173, but when the function runs it only spits out the original value for captiveIR here is the code, any help again would be greatly appreciated:
Code:
Private Sub CommandButton1_Click()
Run Breakeven()
End Sub
Function Breakeven() As Variant
Dim Benefit As Double
Dim CaptiveIR As Double
Dim AVPYr1 As Double
Dim AVPYr2 As Double
Dim AVPYr3 As Double
Dim AVPYr4 As Double
Dim AVPYr5 As Double
Dim ImpactTax As Double
Dim Premium As Double
Dim NetCostCap As Double
Dim InvestCap As Double
Dim InvestOnshore As Double
Dim IncTax As Double
Dim PGTotal As Double
Dim HDDDTotal As Double
CaptiveIR = 0.05
Do Until Benefit <= 0.00000001 And Benefit > -0.00000001
AVPYr1 = CDbl(Worksheets("Investment Income").Range("$C$27").Value)
AVPYr2 = CDbl(Worksheets("Investment Income").Range("C19").Value) + CDbl(Worksheets("Assumptions").Range("B8").Value) * (CDbl(Worksheets("Premium Gross Up").Range("C37").Value) + (AVPYr1 * CaptiveIR) + CDbl(Worksheets("Financial Statements").Range("C39").Value)) + CDbl(Range("D13").Value) + CDbl(Worksheets("Investment Income").Range("D15").Value) + (CDbl(Worksheets("Investment Income").Range("D17").Value) * 0.5)
AVPYr3 = AVPYr2 + (CDbl(Worksheets("Investment Income").Range("D17").Value) * 0.5) + (CaptiveIR * AVPYr2) + CDbl(Worksheets("Assumpions").Range("B8").Value) * (CDbl(Worksheets("Premium Gross Up").Range("C38").Value) + (AVPYr2 * CaptiveIR) + CDbl(Worksheets("Financial Statements").Range("D39").Value)) + CDbl(Worksheets("Investment Income").Range("E13").Value) + CDbl(Worksheets("Investment Income").Range("e15").Value) + (CDbl(Range("e17").Value) * 0.5)
AVPYr4 = AVPYr3 + (CDbl(Worksheets("Investment Income").Range("E17").Value) * 0.5) + (CaptiveIR * AVPYr3) + CDbl(Worksheets("Assumptions").Range("B8").Value) * (CDbl(Worksheets("Premium Gross Up").Range("C39").Value) + (AVPYr3 * CaptiveIR) + CDbl(Worksheets("Financial Statements").Range("E39").Value)) + CDbl(Worksheets("Investment Income").Range("F13").Value) + CDbl(Worksheets("Investment Income").Range("F15").Value) + (CDbl(Worksheets("Investment Income").Range("F17").Value) * 0.5)
AVPYr5 = AVPYr4 + (CDbl(Worksheets("Investment Income").Range("F17").Value) * 0.5) + (CaptiveIR * AVPYr4) + CDbl(Worksheets("Assumptions").Range("B8").Value) * (CDbl(Worksheets("Premium Gross Up").Range("C40").Value) + (AVPYr4 * CaptiveIR) + CDbl(Worksheets("Financial Statements").Range("F39").Value)) + CDbl(Worksheets("Investment Income").Range("G13").Value) + CDbl(Worksheets("Investment Income").Range("G15").Value) + (CDbl(Worksheets("Investment Income").Range("G17").Value) * 0.5)
InvestOnshore = CDbl(Worksheets("Investment Income").Range("Q173").Value) * (AVPYr1 + AVPYr2 + AVPYr3 + AVPYr4 + AVPYr5)
InvestCap = CaptiveIR * (AVPYr1 + AVPYr2 + AVPYr3 + AVPYr4 + AVPYr5)
IncTax = CDbl(Worksheets("Investment Income").Range("H259").Value)
Premium = CDbl(Worksheets("Investment Income").Range("F164").Value)
NetCostCap = CDbl(Worksheets("Investment Income").Range("F165").Value)
ImpactTax = CDbl(Worksheets("Assumptions").Range("B8")) * ((Sheets("Premium Gross Up")) + WorksheetFunction.Sum(Range("H37"), Range("H41")) + (Sheets("High Deductible Detail")) + WorksheetFunction.Sum(Range("C10"), Range("G16")) + (CDbl(Worksheets("Investment Income").Range("Q173").Value) * (AVPYr1 + AVPYr2 + AVPYr3 + AVPYr4 + AVPYr5)))
Benefit = ImpactTax + Premium + NetCostCap + InvestCap + InvestOnshore + IncTax
If Benefit < 0.00000001 Then
CaptiveIR = CaptiveIR + 0.0001
Else: CaptiveIR = CaptiveIR - 0.0001
End If
Loop
Range("AL173").Value = CaptiveIR
End Function
Linear Mode
