Code for long array formula

[chuttus]

Hi guys,

I'm pretty sure, the topic is covered in this forum in the past. I'm looking for a macro code to run the below formula, which is a long array more than 255 characters. I tried solutions calling out Part1 and Part2 formula posted in other threads but it is still giving me an error.

Here is the formula... basically, INDEX is being repeated 10 times. I think it should be split into three part formula, but I don't know the syntax for it. Thanks in advance.

Please Login or Register  to view this content.

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.


To add a file to a post

[chuttus]

Refer to the attached macro workbook. I want to populate Column C in the Report sheet using a long array formula and enable this using a macro code.

[FDibbins]

Without opening your file, I cant really make out what your formula is doing, but a few things comes to mind...
- you are using INDEX on a single cell, not a range - it is normally a range. Perhaps a different function would be better?
- you index the same cell each time, perhaps to shorten the formula, use a short named range for C5?
- You also seem to me MATCHING onto a single cell, too, instead of a range. Again, maybe a different function may be better?
- essentially the same formula is being used each time, just a modified MATCH criteria, which you then combine. Maybe break each 1 into it's own cell, then combine all those cells into 1 cell?

[chuttus]

I have included the file to help you simplify my problem. I appreciate any assistance. Thanks!

[FDibbins]

OK so what is wrong with the formula you have in C2? (does not need to be an array, btw)

[chuttus]

The formula runs fine, but when I tried this out in macro it is giving me an error 'unable to set FormulaArray property of the range class'. You can test it out by running Macro1 in the attached file

[FDibbins]

OK, I guess my real question is - if the formula works, why change to something else?

[chuttus]

Because I'm trying to run this report using macro. Below is the code that is giving me error, when I try to convert the formula to macro script

Please Login or Register  to view this content.

[BoredWorker]

what you want to meet is count all the reject and cancel in one cell and combine the related queue in anther one cells. ("No reject" show if there have no reject")
Here have 2 methods for you,

one is array formula, but i not sure whether you able to use the textjoin function.

Please Login or Register  to view this content.

another is VBA code, if the id is not exist it will turn to "No Such Id"

Please Login or Register  to view this content.

[chuttus]

Awesome, exactly as I wanted. The VBA code works perfectly, however it takes a long time when I tried this on a report with 8000 rows... Is there anything I can add to speed up the run time?

[Kyle123]

Slightly different approach:

Please Login or Register  to view this content.

[BoredWorker]

you may try to sort the data first, and add this line in between beginning and end of code.
Application.ScreenUpdating = False (after sub)
Application.ScreenUpdating = True (before end sub)

and you may try this as well, i use filter instead to avoid check through 8000 rows, but not sure on the time consume.

Please Login or Register  to view this content.

[chuttus]

I tried the filter code and it is taking longer though, So added Application.ScreenUpdating for the previous code and it helped a bit. Thanks!

Also Kyle123, I didn't try your code but thanks for trying!

[Kyle123]

My code will be the fastest, but it doesn't quite so what the others Do, it will give you the results for all line nos. Without the intermediate step of putting the distinct items in the results sheet.

[chuttus]

I tried your code Kyle, it definitely seems faster and as you said it populates all columns. But for a VBA beginner like me, the code seems complicated and very hard to change to fit my current data with additional columns and data.

[chuttus]