Covert Sections of formulas to values after they are calculated

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
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)),"")
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,670
Office Version
  1. 365
Platform
  1. Windows
You can take a whole contiguous range of formulas (like I2:P2), and convert to values with a line of VBA code like this:
VBA Code:
    Range("I2:P2").Value = Range("I2:P2").Value
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235
You can take a whole contiguous range of formulas (like I2:P2), and convert to values with a line of VBA code like this:
VBA Code:
    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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,670
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
and then turn them back on at the very end of my code like this:
VBA Code:
    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.
VBA Code:
    Range("I2:P1100").Value = Range("I2:P1100").Value
 

Twollaston

Board Regular
Joined
May 24, 2019
Messages
235

ADVERTISEMENT

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.

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Try this
VBA Code:
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,711
Messages
5,626,427
Members
416,183
Latest member
IanA

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
Top