E
ExcelMonkey
Tom, I didn't see any "missing libraries, here is the code. It i
basically a routine which calls a large sub routine. The code fails i
the second routine within the Do Until Loop. It fails on the line o
code:
X = Round(Range(Target_Address) - Target, 5)
It highlights the word "Range" and says "Compile error" Sub or Functio
not defined.
See Below............
Sub Run_Breakevens()
Dim t As Integer
Dim r As Variant
Range("BreakEven_Switch") = "Yes"
t = 0
Calculate
For Each r In Range("Breakeven_Range").Rows()
t = t + 1
Do_Breakeven (t)
Next r
Range("BreakEven_Switch") = "No"
Sheets("FSA Sensitivity Control").Select
End Sub
Sub Do_Breakeven(Count As Variant)
Dim Sens_Address As Variant, Amount As Variant, Solve_Address A
Variant
Dim Target_Address As Variant, Target As Variant, Resolution A
Variant
Dim OldAmount As Variant, Finish As Boolean, Delta As Variant
Dim X As Variant, Solution As Variant
Sheets("FSA Sensitivity Control").Select
Sens_Address = Range("Breakeven_Range").Cells(Count, 1).Text
Amount = Range("Breakeven_Range").Cells(Count, 2).Value
Solve_Address = Range("Breakeven_Range").Cells(Count, 3).Text
Target_Address = Range("Breakeven_Range").Cells(Count, 4).Address
Target = Range("Breakeven_Range").Cells(Count, 5).Value
Resolution = Range("Breakeven_Range").Cells(Count, 6).Value
OldAmount = Sheets("Input forecast").Range(Sens_Address)
Sheets("Input forecast").Range(Sens_Address) = Amount
OldSolve_Address = Sheets("Input forecast").Range(Solve_Address)
Finish = False
Delta = -1 * Resolution
Do Until Finish = True
Calculate
X = Round(Range(Target_Address) - Target, 5)
If X = 0 Then Finish = True
If Abs(Delta) < 0.00000000001 And Delta * Resolution > 0 The
Finish = True
If Delta * (Range(Target_Address).Value - Target) > 0 Then Delta
-Delta / 10
Sheets("Input forecast").Range(Solve_Address) = _
Sheets("Input forecast").Range(Solve_Address) + Delta
Loop
Solution = Sheets("Input forecast").Range(Solve_Address).Value
Range("Breakeven_Results").Cells(Count, 1) = Solution
Sheets("Input forecast").Range(Sens_Address) = OldAmount
Sheets("Input forecast").Range(Solve_Address) = OldSolve_Address
Calculate
End Su
basically a routine which calls a large sub routine. The code fails i
the second routine within the Do Until Loop. It fails on the line o
code:
X = Round(Range(Target_Address) - Target, 5)
It highlights the word "Range" and says "Compile error" Sub or Functio
not defined.
See Below............
Sub Run_Breakevens()
Dim t As Integer
Dim r As Variant
Range("BreakEven_Switch") = "Yes"
t = 0
Calculate
For Each r In Range("Breakeven_Range").Rows()
t = t + 1
Do_Breakeven (t)
Next r
Range("BreakEven_Switch") = "No"
Sheets("FSA Sensitivity Control").Select
End Sub
Sub Do_Breakeven(Count As Variant)
Dim Sens_Address As Variant, Amount As Variant, Solve_Address A
Variant
Dim Target_Address As Variant, Target As Variant, Resolution A
Variant
Dim OldAmount As Variant, Finish As Boolean, Delta As Variant
Dim X As Variant, Solution As Variant
Sheets("FSA Sensitivity Control").Select
Sens_Address = Range("Breakeven_Range").Cells(Count, 1).Text
Amount = Range("Breakeven_Range").Cells(Count, 2).Value
Solve_Address = Range("Breakeven_Range").Cells(Count, 3).Text
Target_Address = Range("Breakeven_Range").Cells(Count, 4).Address
Target = Range("Breakeven_Range").Cells(Count, 5).Value
Resolution = Range("Breakeven_Range").Cells(Count, 6).Value
OldAmount = Sheets("Input forecast").Range(Sens_Address)
Sheets("Input forecast").Range(Sens_Address) = Amount
OldSolve_Address = Sheets("Input forecast").Range(Solve_Address)
Finish = False
Delta = -1 * Resolution
Do Until Finish = True
Calculate
X = Round(Range(Target_Address) - Target, 5)
If X = 0 Then Finish = True
If Abs(Delta) < 0.00000000001 And Delta * Resolution > 0 The
Finish = True
If Delta * (Range(Target_Address).Value - Target) > 0 Then Delta
-Delta / 10
Sheets("Input forecast").Range(Solve_Address) = _
Sheets("Input forecast").Range(Solve_Address) + Delta
Loop
Solution = Sheets("Input forecast").Range(Solve_Address).Value
Range("Breakeven_Results").Cells(Count, 1) = Solution
Sheets("Input forecast").Range(Sens_Address) = OldAmount
Sheets("Input forecast").Range(Solve_Address) = OldSolve_Address
Calculate
End Su