The following macro is designed to find a first payment higher than the average payment and 11 payments lower than the average to give 12 payments adding to the same as the weekly payment multiplied by 365 and divided by 7 which invariably introduces non exact amounts at the monthly level. Hope that makes sense!
The following code works but takes hours to run because of the cnt statement at the end of each loop. Without it excel runs for about three seconds and then stops responding. Could it be a stack overflow?
Any help or advice greatly appreciated.
Sub ExactFaster()
Dim A As Double
Dim b As Double
Dim c As Double
Dim d As Double
Dim e As Double
Dim f As Double
Dim cnt As Double
A = Cells(15, 4) * 100
b = Cells(15, 4) * 100
AAA:
c = A - Int(A)
e = 1 - c
d = b - Int(b)
f = 1 - d
If c < 0.01 Or e < 0.01 Then flag = 1
If d < 0.01 Or f < 0.01 Then flagg = 1
If flag = 1 And flagg = 1 Then GoTo BBB
flag = 0: flagg = 0
A = A + 0.01
b = b - 0.01 / 11
'cnt = cnt + 1: Cells(1, 2) = cnt
GoTo AAA
BBB:
Cells(1, 6) = A / 100
Cells(2, 6) = b / 100
End Sub
The following code works but takes hours to run because of the cnt statement at the end of each loop. Without it excel runs for about three seconds and then stops responding. Could it be a stack overflow?
Any help or advice greatly appreciated.
Sub ExactFaster()
Dim A As Double
Dim b As Double
Dim c As Double
Dim d As Double
Dim e As Double
Dim f As Double
Dim cnt As Double
A = Cells(15, 4) * 100
b = Cells(15, 4) * 100
AAA:
c = A - Int(A)
e = 1 - c
d = b - Int(b)
f = 1 - d
If c < 0.01 Or e < 0.01 Then flag = 1
If d < 0.01 Or f < 0.01 Then flagg = 1
If flag = 1 And flagg = 1 Then GoTo BBB
flag = 0: flagg = 0
A = A + 0.01
b = b - 0.01 / 11
'cnt = cnt + 1: Cells(1, 2) = cnt
GoTo AAA
BBB:
Cells(1, 6) = A / 100
Cells(2, 6) = b / 100
End Sub