Macros to replace array formuas?

sreed39

New Member
Joined
Nov 2, 2017
Messages
25
Office Version
  1. 365
Platform
  1. Windows
A new question. I have another spreadsheet which is a conglomeration of data regarding truck trips. Unfortunately the design of the sheet is not conducive to getting data. I have been working on a new concept for this which would allow for sorting of different portions of the trips (basic trip data, fuel add data, issue data and fault code data), but these also span different tests. So at the top I created a section called 'Ongoing test summary). I want to be able to show the total truck data as well as data related to specific test plans (identified as RG##### where the pound symbols represent specific letters.)

The array formulas I use work perfectly, but the workbook could have anywhere from three to 30 different tabs (one for each truck) which then makes the entire workbook very slow and freezes up my system. Wondering if there is a macro or better formulas (and I don't know macros or VBA at all) that would resolve this dilemma.

Appreciate any help you can offer!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Yes, replacing array formulas with VBA code can often speed up the performance of a workbook.

There's many ways to speed up the performance of a workbook that don't necessarily involve VBA. If you aren't familiar with VBA, you might consider doing a web search (excel slow workbook) for alternative things to try first.
 
Upvote 0
I took most of those steps; but they were to no avail. I uploaded teh 64 bit version; turned calculation to manual. But the biggest suggestion is to avoid array formulas - which leads me to this loop again.
 
Upvote 0
If you post a small example that illustrates a typical array formula from your workbook, I’ll try to suggest some VBA code to replace it.
 
Upvote 0
A brief intro to what I do; we run testing programs for vehicles. We have anywhere from 10-35 vehicles each with their own unique data. Within each of those, the vehicle might undergo multiple tests unique to that vehicle. So I may have vehicle ZZ1400 run 13,000 miles under RG13000 and another 18000 miles in RG14000. We put in all of the information about the trip in the rows. But there are also fault codes which are text not numbers; there are issue descriptions to which we assign a number as well as an occurrence count. I am looking for methods to be able to quickly calculate a 'running total' for the vehicle plus each unique test within that vehicle.

Here is an example of the Basic Values I need to compute. Some of them are pretty simple; like total miles, etc.

Total RG13000
Total Shifts Driven =SUM(--(FREQUENCY($E$15:$E$4802,$E$15:$E$4802)>0)) =COUNTIF($A$15:$A$4802,"RG13000")
Total Test Miles =SUM(E15:E4802) =$B$4-($B$3+$B$5)
Total Issue Count =SUM(--(FREQUENCY($L$15:$L$4802,$L$15:$L$4802)>0)) Would need to count when cell A references RG13000
Total Fault Codes =SUM(IF(FREQUENCY(IF(LEN($Y$15:$Y$4802)>0,MATCH($Y$15:$Y$4802,$Y$15:$Y$4802,0),""), IF(LEN($Y$15:$Y$4802)>0,MATCH($Y$15:$Y$4801,$Y$15:$Y$4802,0),""))>0,1))

This would also need to be calculated for each unique test identifier.










<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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