Covert Sections of formulas to values after they are calculated

[Twollaston]

I have a spreadsheet with 8 formulas. These formulas start from Columns I2:P2, and I want to copy these formulas down to I34201:P34201, when I copy the formulas down the sheet will lock up due to too many formula calculations needing to happen. I was wondering if someone has a macro that will paste the formulas in section by section and in each section perform the calculation, and then convert to values. Is this possible? or is there a better way? It's a company project, so I'm limited in the tools available.

Additional Info: It works fine if I do 1080 rows at a time. These formulas are basically dynamic lists, that populate based on a pivot table connected to power BI and then I set up like 4000 named ranges so that I can sumproduct multiple arrays of data. I'm running Excel Version 2010.



My Formulas:

Cell I2
=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))*ROW($A$1:$A$32401)),$G2)),"")

Cell J2
=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))*ROW($A$1:$A$32401)),$G2)),"")

Cell K2
=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))*ROW($A$1:$A$32401)),$G2)),"")

Cell L2
=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))*ROW($A$1:$A$32401)),G2)),"")

Cell M2
=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))*ROW($A$1:$A$32401)),$G2)),"")

Cell N2
=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))*ROW($A$1:$A$32401)),G2)),"")

Cell O2
=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))*ROW($A$1:$A$32401)),$G2)),"")

Cell P2
=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))*ROW($A$1:$A$32401)),G2)),"")

 

[Joe4]

You can take a whole contiguous range of formulas (like I2:P2), and convert to values with a line of VBA code like this:

    Range("I2:P2").Value = Range("I2:P2").Value

 

[Twollaston]

You can take a whole contiguous range of formulas (like I2:P2), and convert to values with a line of VBA code like this:

    Range("I2:P2").Value = Range("I2:P2").Value

Thanks I appreciate that, I first tried it with a loop by writing the formula to the first 1100 lines and then changing it to a value with that line you mentioned, but I was hoping there may be a better way as it wasn't working all the way through before becoming unresponsive

 

[Joe4]

Loops can be killers. They are resource hogs. Whenever running loops, it is often helpful to turn off AutoCalc mode and disable ScreenUpdating while running.

As matter as fact, I put these lines at the beginning of most of my code:

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

and then turn them back on at the very end of my code like this:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

Also, if you have any automated VBA code (event procedures), you probably should disable events while the code is running too.

Also note that you can do multiple rows and columns at once with the line of code I posted, i.e.

    Range("I2:P1100").Value = Range("I2:P1100").Value

 

[Twollaston]

Thanks Joe for your suggestions I appreciate that.

I'm currently working on something like this. It's been a while since I've worked on arrays and I never really had the best knowledge on them.
Would something like this speed it up you think? (see below code)

I'm trying to amend it so that it will write the formula in with a "#" instead of a "=" to all the cells, that way the formula won't calculate. And then I can find and replace the "=" back in. Would something like this work? And is there someone that can help me amend this, my syntax I think is shotty.

arr = Range("I2", "I34201")
  
    For x = LBound(arr, 1) To UBound(arr, 1)
        For i = LBound(arr, 2) To UBound(arr, 2)
            arr(x, i) = "=IFERROR(INDEX(R1C5:R32401C5,AGGREGATE(15,3,((R1C1:R32401C1=RC8&"" - ""&LEFT(R1C[1],2))/(R1C1:R32401C1=RC8&"" - ""&LEFT(R1C[1],2))*ROW(R1C1:R32401C1)),RC7)),"""")"  [COLOR=rgb(184, 49, 47)]Would this work?[/COLOR]
    Next



arr = Range("I2", "P1001")
  
    For x = LBound(arr, 1) To UBound(arr, 1)
        For i = LBound(arr, 2) To UBound(arr, 2)
            arr(x, i) = Replace(arr(x, i), "#", "=")
        Next
    Next

  
Range("I2:P1001").Value = Range("I2:I1001").Value

arr = Range("I1002", "P2001")
  
    For x = LBound(arr, 1) To UBound(arr, 1)
        For i = LBound(arr, 2) To UBound(arr, 2)
            arr(x, i) = Replace(arr(x, i), "#", "=")
        Next
    Next

Range("I2:P1001").Value = Range("I2:I1001").Value
  
   Range("A2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
 
  
  
    Erase arr

 

[DanteAmor]

Try this

Sub test()
With Range("I2", "I34201")
  .FormulaArray = "=IFERROR(INDEX(R1C5:R32401C5,AGGREGATE(15,3,((R1C1:R32401C1=RC8&"" - ""&LEFT(R1C[1],2))/(R1C1:R32401C1=RC8&"" - ""&LEFT(R1C[1],2))*ROW(R1C1:R32401C1)),RC7)),"""")"
  .Value = .Value
End With
End Sub

If it is not an array formula then:

Sub test()
With Range("I2", "I34201")
  .Formula = "=IFERROR(INDEX(R1C5:R32401C5,AGGREGATE(15,3,((R1C1:R32401C1=RC8&"" - ""&LEFT(R1C[1],2))/(R1C1:R32401C1=RC8&"" - ""&LEFT(R1C[1],2))*ROW(R1C1:R32401C1)),RC7)),"""")"
  .Value = .Value
End With
End Sub

 

[Twollaston]

Thanks Dante I appreciate your assistance with this! Big help from both you and joe!

 

[DanteAmor]

Glad we could help & thanks for the feedback