Using FormulaArray to Look Up nth value

**Jlopez21887** · 06-16-2022, 11:48 AM

I receive a data file weekly that's in a format I can not use so I am using VBA to get it into a format to track changes to statuses to projects over the last week. I first sort the data by date then, I am using several array formulas to look up the 1st, 2nd, Nth value.

I am running into two issues currently.

1) The code takes a while to run, mostly the copy formula part. Is this the best way to write this?
2) If there is only 1 or 2 changes for that project on that week. The value that is returned in #NUM. When I wrap the formula in IFERROR(Formula,"") doing it manually it works fine. When I try to write it in the code, i get a:

"Run time 1004 Error - Unable to set the FormulaArray property of the Range Class."

Please Login or Register  to view this content.

Thanks for any feedback,
J

**TMS** · 06-16-2022, 12:34 PM

1) it's probably slow because of the full column references. Ideally, determine the specific range(s).
2) I suspect you need to double up on the quote marks … "" >>> """"

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.