# Thread: VBA code (macro) to replace array formula

1. ## Re: Help needed to find a VBA code (macro)

Just for the records:
Surely this is related to this other thread
https://www.mrexcel.com/forum/excel-...ba-code-2.html

As a solution i suggested a not very efficient formula because i did not quite understand what you wanted, or because i wasn't on a very good day .

The solution with macro should be the best, but only for testing purposes follows another formula.

X2 copied down
=IF(A2="","",INDEX(A\$2:A\$1000,AGGREGATE(15,6,(ROW(A\$2:A\$1000)-ROW(A\$2)+1)/(Q\$2:Q\$1000=AGGREGATE(15,6,1/(1/((S\$2:S\$1000=S2)*(Q\$2:Q\$1000))),1)),1)))

confirmed with just Enter (no need of Ctrl+Shift+Enter)

M.

2. ## Re: Help needed to find a VBA code (macro)

Another formula for testing purposes

X2 copied down
=IF(A2="","",LOOKUP(1,0/FREQUENCY(1,(S\$2:S\$1000=S2)*(Q\$2:Q\$1000)),A\$2:A\$1000))

confirmed with just Enter

M.

3. ## Re: Help needed to find a VBA code (macro)

Good day Marcelo
Thanks for this new formula option. Here my feedback!
The formula works well in my test sheet, but not in my active sheet.
I believe that its due to the fact that I have not everywhere dates in column Q. I'll fill them with dummy dates. and test again.

Marc

4. ## Re: Help needed to find a VBA code (macro)

Hello Marcelo
I have played arround with my sheet and came across the following.
If my Range is above the 379 the result is all "0".
The Range from 2 to 278 seem to work.
Any idea?
Here is the link to my DropBox where you see my file:
https://www.dropbox.com/s/fq88tzdpyd...ula1.xlsm?dl=0

Marc

5. ## Re: Help needed to find a VBA code (macro)

Just checking: Your original question was how to move from a formula approach to a macro approach. Are you now looking to go back to a formula solution?

6. ## Re: Help needed to find a VBA code (macro)

Hi Peter.
No, for the moment the macro approach seems to be the fastest way to get the results in the sheet.
It is just my interest in understanding that formula.
Marc

7. ## Re: Help needed to find a VBA code (macro)

Originally Posted by MSchädler
Hello Marcelo
I have played arround with my sheet and came across the following.
If my Range is above the 379 the result is all "0".
The Range from 2 to 278 seem to work.
Any idea?
Here is the link to my DropBox where you see my file:
https://www.dropbox.com/s/fq88tzdpyd...ula1.xlsm?dl=0

Marc
Cell Q371 contains ???
Delete it and the formula should work

M.

8. ## Re: VBA code (macro) to replace array formula

Also be sure that column Q contains dates (numbers), not text

M.

9. ## Re: VBA code (macro) to replace array formula

Hello Marcelo
Now I really feel stupid.
Sorry I didn't see that (Q371) but it makes sense why it didn't work! And yes I'll make sure Q contains dates.

Thanks again and yes the sheet works now.
Marc

10. ## Re: Help needed to find a VBA code (macro)

Originally Posted by Peter_SSs
Just checking: Your original question was how to move from a formula approach to a macro approach. Are you now looking to go back to a formula solution?
Originally Posted by MSchädler
Hi Peter.
No, for the moment the macro approach seems to be the fastest way to get the results in the sheet.
It is just my interest in understanding that formula.
Marc

Peter and Marc

I apologise.

It seems i've caused some confusion. I should have posted the alternative formula in the mentioned other thread. I'm doing this just now.

M.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•