Batman,
I am not sure exactly what you mean by "snippet," but as that part of the code is only run if the "Create Family Relationships" is checked; I believe it would be considered a snippet.
It is currently run after the varOverlay_Data array is written to the Overlay sheet, because the min3 functions uses the Worksheet function CountIf and that functions requires the data to be in the sheet.
I have attached another example file with the progress bar removed, fewer REF_Data rows and some comments in the code.
With the sample file, the code will run very fast. I assume you will readily see, that if this "Create Family Relationships" is used on an Overlay sheet with thousands or tens of thousands of records, it would again take a long time to loop through all of the data in the Overlay sheet records.
It there were some way to achieve this "snippet' result while still working with the array, then that problem might be resolved. Do you think adding the overlay array record values to a dictionary as they were created would give us the opportunity to achieve an equivalent "CountIf" in the snippet and min3 process with the Overlay_data array before writing to the Overlay sheet? If so, do you think doing so would take less time.
I am not asking to be given the fish. I am making some progress on determining the answers to those questions myself. Give me some time. If I get stuck, I reach out. If not, I will post the solution I find. Thanks for all of your interest and help.
Last edited by gmaxey; 02-03-2025 at 08:41 AM.
|