Power Query Course in Spanish
Page 3 of 3 FirstFirst 123
Results 21 to 30 of 30

Thread: VBA code (macro) to replace array formula

  1. #21
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,796
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default 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. #22
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,796
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default 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. #23
    Board Regular
    Join Date
    Apr 2017
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Thanks for your effort.
    Marc

  4. #24
    Board Regular
    Join Date
    Apr 2017
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Looking forward to your answer.
    Marc

  5. #25
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    37,458
    Post Thanks / Like
    Mentioned
    55 Post(s)
    Tagged
    9 Thread(s)

    Default 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?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #26
    Board Regular
    Join Date
    Apr 2017
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Thanks for asking.
    Marc

  7. #27
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,796
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Help needed to find a VBA code (macro)

    Quote Originally Posted by MSchädler View Post
    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

    Looking forward to your answer.
    Marc
    Cell Q371 contains ???
    Delete it and the formula should work

    M.

  8. #28
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,796
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA code (macro) to replace array formula

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

    M.

  9. #29
    Board Regular
    Join Date
    Apr 2017
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #30
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    14,796
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Help needed to find a VBA code (macro)

    Quote Originally Posted by Peter_SSs View Post
    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?
    Quote Originally Posted by MSchädler View Post
    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.
    Thanks for asking.
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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