Forum Discussion

BitterJug's avatar
BitterJug
Copper Contributor
Jan 18, 2022

Converting dynamic array formula to VBA version?

Hi all,

I'm currently using the following daf in C4 =XLOOKUP(C1#&ROUND(A4:A148,5),'All Activity'!A:A&ROUND('All Activity'!B:B,5),'All Activity'!C:C,"")

 

Due to the size of my data set, this formula is taking rather a long time to complete. Does anyone know how I can convert it to a VBA solution? I want to do this as it is potentially faster, avoids recalculation when I don't need it, and frees up cells in the range for additional work.

The formula exists on a sheet called "sheet1" and uses "all Activity" as its source data.

 

The data in all activity is simply three columns, the first two columns are the axes on sheet1 (already exist on the sheet). Column 1 (part IDs) exists as an axes in sheet 1 starting at C1 and continuing through row 1 and continuing. Column 2 (date times) isn't directly copied, but used as a reference point for where things should be placed in relation to the existing times on Sheet1 starting in A4. Column 3 is the data that gets put in the area between the two axes starting in C4 and continuing as needed.

If I've not made sense or more information is required, please ask as I could really use some help on this one 🙂

excel
Formulas and Functions
Macros and VBA
office 365

2 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 01, 2022

    BitterJug 

    I realize this is late but this might be helpful to someone coming across this discussion.

     

    Have you tried making these bolded references specific and not looking at entire columns?  I think those might be responsible for the calculation crunch.

    =XLOOKUP(C1#&ROUND(A4:A148,5),'All Activity'!A:A&ROUND('All Activity'!B:B,5),'All Activity'!C:C,"")

    • mtarler's avatar
      mtarler
      Silver Contributor
      Aug 01, 2022

      Patrick2788  makes a great point and I would add to that:

      a) I would format the data on the 'All Activity' sheet as a Table so that the reference can use a Table reference which will automatically expand with added data rows without the need to reference the whole column (e.g. Table1[colA] would reference the column called colA in the table called Table1)

      b) I would add a column in that table to do the A:A&ROUND(B:B,5) calculation.  When you append the data set it won't have to calculate all the prior rows

       

Resources