VBA code failing and miscalculating when running high numbers of lines (200+) but ok when doing it line by line

[MissVBAquery]

Hi,
Is there any way to help solve this issue. Currently I have a tab called 'BATCH' where I paste all my data. VBA code is written to calculate different versions of premiums by different permutations (code below). Suddenly the VBA miscalculates (so putting 365 values where 30 values should be, and teh other way round) when trying to run 200+ lines. When doing it line by line it works fine. It used to run fine on 5k lines +. Is there anything I can do to fix/improve the code so it runs on 5k lines again? Is there something like cache files but for Excel? Restarting Excel/comp didn't help

Please help with your suggestions

Sub Batch()
    Sheets("Batch").Select
    Range("A2").Select
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Do Until ActiveCell = "" Or i = 10000
    
    ''''''''''''''''''''''' POLICY INFORMATION '''''''''''''''''''''''
    Worksheets("Calculator").Range("C3", "C5").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 3)))
    Worksheets("Calculator").Range("C6", "C6").Value = Range(ActiveCell.Offset(0, 14), ActiveCell.Offset(0, 14)).Value
    
    '''''''''''''''''''''' BASE RATE '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 1 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C9", "C9").Value = Range(ActiveCell.Offset(0, 4), ActiveCell.Offset(0, 4)).Value
    
    
    '''''''''''''''''''''' DRIVER 1 '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 10 - 160 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C12", "C19").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 5), ActiveCell.Offset(0, 12)))
    Worksheets("Calculator").Range("C20", "D20").Value = Range(ActiveCell.Offset(0, 13), ActiveCell.Offset(0, 14)).Value
     Worksheets("Calculator").Range("C21", "C33").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 15), ActiveCell.Offset(0, 27)))
    
    '''''''''''''''''''''' TABLE 170 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C34", "D34").Value = Range(ActiveCell.Offset(0, 28), ActiveCell.Offset(0, 29)).Value
    
     '''''''''''''''''''''' TABLE 180, 190 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C35", "C36").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 31), ActiveCell.Offset(0, 32)))
    
    ''''''''''''''''''''''' TABLE 225 ''''''''''''''''''''''
    Worksheets("Calculator").Range("C39", "C39").Value = Range(ActiveCell.Offset(0, 33), ActiveCell.Offset(0, 33)).Value
    
    ''''''''''''''''''''''' TABLE 230, 240, 250 '''''''''''''''''''''''
    Worksheets("Calculator").Range("D40", "D42").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 34), ActiveCell.Offset(0, 36)))
    
    ''''''''''''''''''''''' TABLE 255 - 256 ''''''''''''''''''''''
    Worksheets("Calculator").Range("C43", "D43").Value = Range(ActiveCell.Offset(0, 37), ActiveCell.Offset(0, 38)).Value
    Worksheets("Calculator").Range("C44", "D44").Value = Range(ActiveCell.Offset(0, 39), ActiveCell.Offset(0, 40)).Value
    
    
    ''''''''''''''''''''''' MOTORING CONVICTIONS 1 '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 430 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C49", "D49").Value = Range(ActiveCell.Offset(0, 41), ActiveCell.Offset(0, 42)).Value
    Worksheets("Calculator").Range("C50", "D50").Value = Range(ActiveCell.Offset(0, 43), ActiveCell.Offset(0, 44)).Value
    Worksheets("Calculator").Range("C51", "D51").Value = Range(ActiveCell.Offset(0, 45), ActiveCell.Offset(0, 46)).Value
    Worksheets("Calculator").Range("C52", "D52").Value = Range(ActiveCell.Offset(0, 47), ActiveCell.Offset(0, 48)).Value
    Worksheets("Calculator").Range("C53", "D53").Value = Range(ActiveCell.Offset(0, 49), ActiveCell.Offset(0, 50)).Value
    
    
    '''''''''''''''''''''' DRIVER 2 '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 10 - 30 '''''''''''''''''''''''
    Worksheets("Calculator").Range("O12", "O14").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 51), ActiveCell.Offset(0, 53)))
    
     ''''''''''''''''''''''' TABLE 45 '''''''''''''''''''''''
    Worksheets("Calculator").Range("O21", "O21").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 54), ActiveCell.Offset(0, 54)))
    
    
    ''''''''''''''''''''''' TABLE 150 - 160 '''''''''''''''''''''''
    Worksheets("Calculator").Range("O32", "O33").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 55), ActiveCell.Offset(0, 56)))
    
    '''''''''''''''''''''' TABLE 170 '''''''''''''''''''''''
    Worksheets("Calculator").Range("O34", "P34").Value = Range(ActiveCell.Offset(0, 57), ActiveCell.Offset(0, 58)).Value
    
    ''''''''''''''''''''''' TABLE 225 ''''''''''''''''''''''
    Worksheets("Calculator").Range("O39", "O39").Value = Range(ActiveCell.Offset(0, 59), ActiveCell.Offset(0, 59)).Value
    
    ''''''''''''''''''''''' TABLE 256 ''''''''''''''''''''''
    Worksheets("Calculator").Range("O43", "P43").Value = Range(ActiveCell.Offset(0, 60), ActiveCell.Offset(0, 61)).Value

    
    ''''''''''''''''''''''' MOTORING CONVICTIONS 2 '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 430 '''''''''''''''''''''''
    Worksheets("Calculator").Range("O49", "P49").Value = Range(ActiveCell.Offset(0, 62), ActiveCell.Offset(0, 63)).Value
    Worksheets("Calculator").Range("O50", "P50").Value = Range(ActiveCell.Offset(0, 64), ActiveCell.Offset(0, 65)).Value
    Worksheets("Calculator").Range("O51", "P51").Value = Range(ActiveCell.Offset(0, 66), ActiveCell.Offset(0, 67)).Value
    Worksheets("Calculator").Range("O52", "P52").Value = Range(ActiveCell.Offset(0, 68), ActiveCell.Offset(0, 69)).Value
    Worksheets("Calculator").Range("O53", "P53").Value = Range(ActiveCell.Offset(0, 70), ActiveCell.Offset(0, 71)).Value
    
    '''''''''''''''''''''' DRIVER 3 '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 10 - 30 '''''''''''''''''''''''
    Worksheets("Calculator").Range("AB12", "AB14").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 72), ActiveCell.Offset(0, 74)))
    
    ''''''''''''''''''''''' TABLE 45'''''''''''''''''''''''
    Worksheets("Calculator").Range("AB21", "AB21").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 75), ActiveCell.Offset(0, 75)))
    
    ''''''''''''''''''''''' TABLE 150 - 160 '''''''''''''''''''''''
    Worksheets("Calculator").Range("AB32", "AB33").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 76), ActiveCell.Offset(0, 77)))
    
    '''''''''''''''''''''' TABLE 170 '''''''''''''''''''''''
    Worksheets("Calculator").Range("AB34", "AC34").Value = Range(ActiveCell.Offset(0, 78), ActiveCell.Offset(0, 79)).Value

    ''''''''''''''''''''''' TABLE 225 ''''''''''''''''''''''
    Worksheets("Calculator").Range("AB39", "AB39").Value = Range(ActiveCell.Offset(0, 80), ActiveCell.Offset(0, 80)).Value
    
    ''''''''''''''''''''''' TABLE 256 ''''''''''''''''''''''
    Worksheets("Calculator").Range("AB43", "AC43").Value = Range(ActiveCell.Offset(0, 81), ActiveCell.Offset(0, 82)).Value

    
    ''''''''''''''''''''''' MOTORING CONVICTIONS 3 '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 430 '''''''''''''''''''''''
    Worksheets("Calculator").Range("AB49", "AC49").Value = Range(ActiveCell.Offset(0, 83), ActiveCell.Offset(0, 84)).Value
    Worksheets("Calculator").Range("AB50", "AC50").Value = Range(ActiveCell.Offset(0, 85), ActiveCell.Offset(0, 86)).Value
    Worksheets("Calculator").Range("AB51", "AC51").Value = Range(ActiveCell.Offset(0, 87), ActiveCell.Offset(0, 88)).Value
    Worksheets("Calculator").Range("AB52", "AC52").Value = Range(ActiveCell.Offset(0, 89), ActiveCell.Offset(0, 90)).Value
    Worksheets("Calculator").Range("AB53", "AC53").Value = Range(ActiveCell.Offset(0, 91), ActiveCell.Offset(0, 92)).Value
    
    
    
    ''''''''''''''''''''''' POLICY '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 270 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C58", "C58").Value = Range(ActiveCell.Offset(0, 93), ActiveCell.Offset(0, 93)).Value
    
    ''''''''''''''''''''''' TABLE 300 '''''''''''''''''''''''
    Worksheets("Calculator").Range("E61", "E61").Value = Range(ActiveCell.Offset(0, 94), ActiveCell.Offset(0, 94)).Value
    
    ''''''''''''''''''''''' TABLE 330 - 420 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C64", "C73").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 95), ActiveCell.Offset(0, 104)))


    ''''''''''''''''''''''' CAP AND COLLAR (PREVIOUS POLICY DURATION AND PREVIOUS ACCEPTABILITY ONLY) '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 475 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C77", "C78").Value = WorksheetFunction.Transpose(Range(ActiveCell.Offset(0, 105), ActiveCell.Offset(0, 106)))
    
    
    ''''''''''''''''''''''' PROTECTED NCD '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 290 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C87", "C87").Value = Range(ActiveCell.Offset(0, 113), ActiveCell.Offset(0, 113)).Value
    
    '''''''''''''''''''''''TENURE INDEX '''''''''''''''''''''''
    ''''''''''''''''''''''' TABLE 500 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C90", "C90").Value = Range(ActiveCell.Offset(0, 114), ActiveCell.Offset(0, 114)).Value
    
    
    
    ''''''''''''''''''''''' NETPREMIUMTPO30 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = "DAYS_30"
    Worksheets("Calculator").Range("C5", "C5").Value = "Third Party Only"
    Worksheets("Calculator").Range("C79", "C79").Value = Range(ActiveCell.Offset(0, 107), ActiveCell.Offset(0, 107)).Value
    Worksheets("Calculator").Calculate
    ''''''''''''''''''''''' NET PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 115).Value = Worksheets("Calculator").Range("E97").Value
    '''''''''''''''''''''' GROSS PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 116).Value = Worksheets("Calculator").Range("E101").Value
    '''''''''''''''''''''' NCD PROTECTION '''''''''''''''''''''''
    ActiveCell.Offset(0, 117).Value = Worksheets("Calculator").Range("E97").Value - (Worksheets("Calculator").Range("E97").Value / Worksheets("Calculator").Range("E87").Value)

    ''''''''''''''''''''''' NETPREMIUMTPO365 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = "DAYS_365"
    Worksheets("Calculator").Range("C5", "C5").Value = "Third Party Only"
    Worksheets("Calculator").Range("C79", "C79").Value = Range(ActiveCell.Offset(0, 108), ActiveCell.Offset(0, 108)).Value
    Worksheets("Calculator").Calculate
    ''''''''''''''''''''''' NET PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 118).Value = Worksheets("Calculator").Range("E97").Value
    '''''''''''''''''''''' GROSS PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 119).Value = Worksheets("Calculator").Range("E101").Value
    ''''''''''''''''''''''' NCD PROTECTION '''''''''''''''''''''''
    ActiveCell.Offset(0, 120).Value = Worksheets("Calculator").Range("E97").Value - (Worksheets("Calculator").Range("E97").Value / Worksheets("Calculator").Range("E87").Value)


    ''''''''''''''''''''''' NETPREMIUMCOMP30 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = "DAYS_30"
    Worksheets("Calculator").Range("C5", "C5").Value = "Comprehensive"
    Worksheets("Calculator").Range("C79", "C79").Value = Range(ActiveCell.Offset(0, 109), ActiveCell.Offset(0, 109)).Value
    Worksheets("Calculator").Calculate
    ''''''''''''''''''''''' NET PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 121).Value = Worksheets("Calculator").Range("E97").Value
    '''''''''''''''''''''' GROSS PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 122).Value = Worksheets("Calculator").Range("E101").Value
    ''''''''''''''''''''''' NCD PROTECTION '''''''''''''''''''''''
    ActiveCell.Offset(0, 123).Value = Worksheets("Calculator").Range("E97").Value - (Worksheets("Calculator").Range("E97").Value / Worksheets("Calculator").Range("E87").Value)


    ''''''''''''''''''''''' NETPREMIUMCOMP365 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = "DAYS_365"
    Worksheets("Calculator").Range("C5", "C5").Value = "Comprehensive"
    Worksheets("Calculator").Range("C79", "C79").Value = Range(ActiveCell.Offset(0, 110), ActiveCell.Offset(0, 110)).Value
    
    Worksheets("Calculator").Calculate
    ''''''''''''''''''''''' NET PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 124).Value = Worksheets("Calculator").Range("E97").Value
    '''''''''''''''''''''' GROSS PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 125).Value = Worksheets("Calculator").Range("E101").Value
    ''''''''''''''''''''''' NCD PROTECTION '''''''''''''''''''''''
    ActiveCell.Offset(0, 126).Value = Worksheets("Calculator").Range("E97").Value - (Worksheets("Calculator").Range("E97").Value / Worksheets("Calculator").Range("E87").Value)


    ''''''''''''''''''''''' NETPREMIUMCOMPPLUS30 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = "DAYS_30"
    Worksheets("Calculator").Range("C5", "C5").Value = "Comprehensive Plus"
    Worksheets("Calculator").Range("C79", "C79").Value = Range(ActiveCell.Offset(0, 111), ActiveCell.Offset(0, 111)).Value
    Worksheets("Calculator").Calculate
    ''''''''''''''''''''''' NET PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 127).Value = Worksheets("Calculator").Range("E97").Value
    '''''''''''''''''''''' GROSS PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 128).Value = Worksheets("Calculator").Range("E101").Value
    ''''''''''''''''''''''' NCD PROTECTION '''''''''''''''''''''''
    ActiveCell.Offset(0, 129).Value = Worksheets("Calculator").Range("E97").Value - (Worksheets("Calculator").Range("E97").Value / Worksheets("Calculator").Range("E87").Value)


    ''''''''''''''''''''''' NETPREMIUMCOMPPLUS365 '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = "DAYS_365"
    Worksheets("Calculator").Range("C5", "C5").Value = "Comprehensive Plus"
    Worksheets("Calculator").Range("C79", "C79").Value = Range(ActiveCell.Offset(0, 112), ActiveCell.Offset(0, 112)).Value
    Worksheets("Calculator").Calculate
    ''''''''''''''''''''''' NET PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 130).Value = Worksheets("Calculator").Range("E97").Value
    ''''''''''''''''''''' GROSS PREMIUM '''''''''''''''''''''''
    ActiveCell.Offset(0, 131).Value = Worksheets("Calculator").Range("E101").Value
    ''''''''''''''''''''''' NCD PROTECTION '''''''''''''''''''''''
    ActiveCell.Offset(0, 132).Value = Worksheets("Calculator").Range("E97").Value - (Worksheets("Calculator").Range("E97").Value / Worksheets("Calculator").Range("E87").Value)


    ''''''''''''''''''''''' PUBLIC LIABILITY '''''''''''''''''''''''
    ActiveCell.Offset(0, 133).Value = 4.11
    ActiveCell.Offset(0, 134).Value = 50
    
    ''''''''''''''''''''''' ACCEPTABILITY '''''''''''''''''''''''
    ActiveCell.Offset(0, 135).Value = Worksheets("Calculator").Range("K84").Value
    
    ''''''''''''''''''''''' SOLD '''''''''''''''''''''''
    Worksheets("Calculator").Range("C4", "C4").Value = Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 2)).Value
    Worksheets("Calculator").Range("C5", "C5").Value = Range(ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 3)).Value
    Worksheets("Calculator").Calculate
    
    ''''''' FOR 30 DAY POLICIES SCALE THE PERIL PREMIUMS DOWN '''''
    If Range(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 2)).Value = "DAYS_365" Then
    ActiveCell.Offset(0, 136).Value = Worksheets("Calculator").Range("F84").Value
    ActiveCell.Offset(0, 137).Value = Worksheets("Calculator").Range("G84").Value
    ActiveCell.Offset(0, 138).Value = Worksheets("Calculator").Range("H84").Value
    ActiveCell.Offset(0, 139).Value = Worksheets("Calculator").Range("I84").Value
    ActiveCell.Offset(0, 140).Value = Worksheets("Calculator").Range("J84").Value
    Else
    ActiveCell.Offset(0, 136).Value = Worksheets("Calculator").Range("F84").Value / 365 * 30
    ActiveCell.Offset(0, 137).Value = Worksheets("Calculator").Range("G84").Value / 365 * 30
    ActiveCell.Offset(0, 138).Value = Worksheets("Calculator").Range("H84").Value / 365 * 30
    ActiveCell.Offset(0, 139).Value = Worksheets("Calculator").Range("I84").Value / 365 * 30
    ActiveCell.Offset(0, 140).Value = Worksheets("Calculator").Range("J84").Value / 365 * 30
    End If

    ActiveCell.Offset(1, 0).Select
    i = i + 1
    Loop
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[DanteAmor]

Hello and welcome to MrExcel board!

I recommend using matrices to perform all calculations.
With the use of matrices the calculations will be carried out in memory and not on the sheet.
The macro will be more efficient and faster.

I will try to explain how the calculation with matrices works.

Your macro passes values from the "Batch" sheet to the "Calculator" sheet.
In the "Calculator" sheet, the calculations are performed on the sheet and the results are returned to the "Batch" sheet.

In my example, 3 cells go from Batch to Calculator
The values of the 3 cells are added in cell B5.
The result of B5 is divided by 365 and the result goes into B8:

DANTE AMOR
	A	B
1		Data
2
3
4
5	Sum	0
6
7
8	Product	0
Calculator

Cell Formulas
Range	Formula
B5	B5	=SUM(B2:B4)
B8	B8	=B5/365

The result of B5 goes in E2 (Batch sheet) and the result of B8 in F2 (Batch sheet). And so for each row of the Batch sheet

The following macro does something similar to what your macro does. Calculate the results on the sheets:

Sub SheetCalculation()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long
  
  'set each sheet in an object
  Set sh1 = Sheets("Batch")
  Set sh2 = Sheets("Calculator")
  
  For i = 2 To sh1.Range("A" & Rows.Count).End(3).Row
    'move values to calculator
    sh2.Range("B2").Value = sh1.Range("B" & i).Value
    sh2.Range("B3").Value = sh1.Range("C" & i).Value
    sh2.Range("B4").Value = sh1.Range("D" & i).Value
    
    'move results to batch
    sh1.Range("E" & i).Value = sh2.Range("B5")
    sh1.Range("F" & i).Value = sh2.Range("B8")
  Next
End Sub

-----------------------------------------------------------------------------
Now I will show you how to obtain the same results, but performing the calculations in the matrix and in memory.

Sub ArrayCalculation()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long
  Dim dSum As Double, dPro As Double
  
  'set each sheet in an object
  Set sh1 = Sheets("Batch")
  Set sh2 = Sheets("Calculator")
  
  'Pass data from batch sheet to an matrix
  a = sh1.Range("A2:D" & sh1.Range("A" & Rows.Count).End(3).Row).Value
  'redim ouput matrix
  ReDim b(1 To UBound(a, 1), 1 To 2)
  
  For i = 1 To UBound(a, 1)
    dSum = a(i, 2) + a(i, 3) + a(i, 4)      'sum 3 cells
    dPro = dSum / 365                       'divide
    
    b(i, 1) = dSum                          'Pass the results to a second matrix 'b'
    b(i, 2) = dPro
  Next
  
  'Pass results from array to Batch sheet
  sh1.Range("E2").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

----------------------------------------------------------------------------
With the above, it is not necessary to turn off the sheet calculations, nor is it necessary to turn off the screen update.

5000 lines will be processed immediately.

----------------------------------------------------------------------------
Definitely need to make a new macro. You can try it. If you have problems, I will be happy to help you, but you will have to upload your file to the cloud, it can be Google Drive or Dropbox. If you have sensitive information, replace it with generic values. What I need to see are the formulas you have in the Calculator sheet to reproduce them with VBA code.
Your file should include 2 or 3 examples with the results.