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

MissVBAquery

New Member
Joined
Jan 31, 2025
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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

VBA Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
1738362063535.png

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
AB
1Data
2
3
4
5Sum0
6
7
8Product0
Calculator
Cell Formulas
RangeFormula
B5B5=SUM(B2:B4)
B8B8=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:
VBA Code:
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.

VBA Code:
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.
 
Upvote 0
great, thank you!

My calculations in the "calculator" sheet try to match the values from other tabs. For example:
=INDEX(INDIRECT($A12&"!$B$4:$I$1000"),MATCH($C12,INDIRECT($A12&"!$B$4:$B$1000"),0),MATCH(F$8,INDIRECT($A12&"!$B$4:$I$4"),0))
or
=INDEX(INDIRECT($A20&"!$A$4:$I$1000"),MATCH($C20&"-"&$D20,INDIRECT($A20&"!$A$4:$A$1000"),0),MATCH(F$8,INDIRECT($A20&"!$A$4:$I$4"),0))
or
=INDEX(INDIRECT($A61&"!$A$4:$J$1000"),MATCH($C61&$D61&$E61,INDIRECT($A61&"!$A$4:$A$1000"),0),MATCH(G$8,INDIRECT($A61&"!$A$4:$J$4"),0))

there are some simpler formulas, too butt the ones above are probably the most difficult ones

=IF(OR(C77="", C78="", C79="", C78="DECLINE", C77="DAYS_30", MAX(D43, D44, P43, P44, AC43, AC44) > 0), E88, MAX(MIN(E88,C79*G77), C79*F77))
 
Upvote 0
For the in-memory process to work, it is necessary to calculate all the formulas in VBA.
Did you try to calculate all your formulas in VBA?

If you want me to help you with the new macro, you will have to upload your file to the cloud, so that I can analyze each of the formulas and solve them with VBA.

You could upload a copy of your file to a free site such dropbox or googledrive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
If the workbook contains confidential information, you could replace it with generic data.


🤗
 
Upvote 0

Forum statistics

Threads
1,226,264
Messages
6,189,928
Members
453,582
Latest member
Browny2821

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