VBA Evaluate Array then imput into range adjusting forumla in Array down with the Range

[fireguy7]

Hello and thank you in advance,

What I'm trying to do is have VBA calculate an Array Formula and then input into a range which is pretty straight forward. The issue is the Array formula doesn't adjust down the range.

for the example sheet I've attached here is the code that'll input the information I currently have.

Please Login or Register  to view this content.

Issue is it inputs A2 as the reference cell in the full range.

for the attached what I want is for A2 to move to A3 for the next cell then A4 for the 3rd cell and so forth.

Was thinking a loop tell blank but the issue is the actual sheet will have a 200k lines so I might run into performance issues if I loop.

any help is appreciated.

[Arkadi]

Hi fireguy,

Give this a shot?

Please Login or Register  to view this content.

[fireguy7]

that worked!!

the only thing that I could see as an issue is it's using cell to do the evaluation so when I drop the Array down over 200k lines it might killthe sheet. I'm going to try it on the large one then close or respond after.

I see what you did as well so I'll know in the future.

thanks ton!

[fireguy7]

Worked with minimal performance lag. Only took about 20 seconds to run the array over 200k lines!!

Your the best.

[Arkadi]

Thanks for the detailed feedback, the rep, and marking the thread as solved!

Glad I could help