VBA code (macro) to replace array formula
VBA code (macro) to replace array formula
Page 1 of 3 123 LastLast
Results 1 to 10 of 30

Thread: VBA code (macro) to replace array formula

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

    Post VBA code (macro) to replace array formula

     
    I use this array formula in an excel sheet. The formula works fine in a small data base.
    The database is currently 600 rows long and growing and now it takes time to calculate.
    I think that a macro instead of the array formula could help to speed up the calculation.
    Can somebody help me finding the macro code?

    This is the general setup of my sheet:

    Row/column A to P Q R S T to W X
    1 Dates Project name Array formula
    2 20.09.2017 Paris [=(xxx)]

    This is the array formula in column X, limited to 1000 rows for speed

    english:
    [=IF(A2="","",IF(COUNTIF(S$2:S$1000,S2)=1,SMALL(IF(A$2:A$1000<>"",IF(ISNA(MATCH(A$2:$A$1000,X$1:X1,0)),A$2:A$1000)),1),INDEX(A$2:A$1000,MATCH(MIN(IF(S$2:S$1000=S2,Q$2:Q$1000)),IF(S$2:S$ 1000=S2,Q$2:Q$1000),0))))]
    german:
    [=WENN(A2="";"";WENN(ZÄHLENWENN(S$2:S$1000;S2)=1;KKLEINSTE(WENN(A$2:A$1000<>"";WENN(ISTNV(VERGLEICH(A$2:$A$1000;X$1:X1;0));A$2:A$1000));1);INDEX(A$2:A$1000;VERGLEICH(MIN(WENN(S$2:S$1000=S2;Q$2:Q$100 0));WENN(S$2:S$1000=S2;Q$2:Q$1000);0))))]


    Thanks for any suggestions.
    M.
    Last edited by MSchädler; Sep 21st, 2017 at 12:23 AM. Reason: formula in english

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,422
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

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

    From that post I cannot tell what you have, exactly where it is or what you are trying to do. Any chance of a small set of actual data (in a form we can see what & where it is - see my signature block below), the expected results & an explanation of how the expected results would be obtained manually?
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - 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

  3. #3
    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 Peter

    Sorry for my unclear question.
    I will send you the short version of my excel file this evening. And thanks in advance for your interest and help.
    Marc

  4. #4
    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
    Sorry, somehow I'm not capable to send you the information as you requested. I don't know how to send you my work file, so that you see what I'm trying to achieve.
    Do you have some more information for me on how to send you (signature block) my excel test file? I have not done this before.
    Marc

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,422
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

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

    Did you click the "Look here" link in my signature block?
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - 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. #6
    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 Peter

    I hope that this is the way to send you part of the excel table with the contents.
    The excel table below automatically counts in colomn A the row line. This is needed so that the "array formula" (X) finds what row number to give to a re-occurring project name (S) when the date entry (Q) is the oldest in that column.

    My goal. I would like to have a macro to replace the array formula (X) and execute as soon as there is an entry in column S - Rollout Projekt. I believe that this would speed up the calculation time, especially when you have more than 1000 rows.
    Guideline to the sheet: once an entry is made, approved and saved, that entry will never be deleted or changed again. Changes will only be made by copy/paste or new entry. The date is always =today().

    I hope this makes my question understandable.
    Thank you for your help
    M.


    x
    yz
    Search Value5
    a13197
    Count3
    b1555
    Values At:RowColc984

    byd11515

    bz
    DB Nummer Prozess

    (drop down)
    Project Typ (kann)
    (drop down)
    Project ID
    (aut.)
    (drop down)
    ISP
    (muss ab FS7)
    Datum
    Eintrag (aut.)
    Cluster
    (kann)
    Rollout-Projekt
    (muss)
    GSM-R Strecken
    (aut.)
    Bahn-Linie(n)
    (kann)
    Funnel-Status (FS)




    (muss und drop down)
    Prozess

    (drop down)
    Project Typ (kann)
    (drop down)
    Project ID
    (aut.)
    (drop down)
    ISP Date
    entry (aut.)
    Cluster Rollout-Project G-Strecken
    (aut.)
    Lines Funnel-Status (FS) most recent entry Project ID
    (aut.)
    Array
    1 05.02.2016 Paris 1 5
    2 05.10.2016 Rome # 2
    3 23.02.2017 New York 1 7
    4 01.02.2016 Toronto 1 4
    5 05.02.2015 Paris # 5
    6 05.10.2017 Rome 1 2
    7 23.02.2014 New York # 7
    8 01.02.2016 Toronto 1 4
    9 05.02.2016 Paris 1 5
    10 05.10.2016 Rome # 2
    11 23.02.2017 New York 1 7
    12 01.02.2016 Toronto 1 4





    dy

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,422
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by MSchädler View Post
    [FONT=Arial][SIZE=2]I hope that this is the way to send you part of the excel table with the contents.
    It is better, but not the best. Compare how easy it is to tell what is where in what you have posted, compared to what I have posted below. Note also that I have hidden some irrelevant columns to make the screen shot even simpler - yet another advantage of the methods I am trying to direct you to, but you aren't even saying if you have tried any of them and if so what went wrong.

    I am not familiar with exactly what you are trying to do, so I have some more questions.

    1. Do I have the correct layout?

    2. Are they actual dates (numbers) in column Q or are they text that look like dates? (Below, I have converted them to actual dates in my local format)

    3. As an example, how do you (manually) get 5 for the first value in column X? (If I apply your formula from post 1 to row 2, it returns #N/A for me)

    If I don't have the correct layout and/or the results for this sample data are incorrect, or you could demonstrate better with another small set of dummy sample data, please do so - preferably with one of the HTML Makers!

    Excel 2016 (Windows) 32 bit
    ABPQRSTUVWX
    1DB NummerProzessISPDateClusterRollout-ProjectG-StreckenLinesFunnel-Status (FS)most recent entryProject ID
    2105/02/2016Paris15
    3205/10/2016Rome#2
    4323/02/2017New York17
    5401/02/2016Toronto14
    6505/02/2015Paris#5
    7605/10/2017Rome12
    8723/02/2014New York#7
    9801/02/2016Toronto14
    10905/02/2016Paris15
    111005/10/2016Rome#2
    121123/02/2017New York17
    131201/02/2016Toronto14

    Sheet6



    Last edited by Peter_SSs; Sep 22nd, 2017 at 01:58 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - 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

  8. #8
    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)

    Quote Originally Posted by Peter_SSs View Post
    It is better, but not the best. Compare how easy it is to tell what is where in what you have posted, compared to what I have posted below. Note also that I have hidden some irrelevant columns to make the screen shot even simpler - yet another advantage of the methods I am trying to direct you to, but you aren't even saying if you have tried any of them and if so what went wrong.

    I am not familiar with exactly what you are trying to do, so I have some more questions.

    1. Do I have the correct layout?

    2. Are they actual dates (numbers) in column Q or are they text that look like dates? (Below, I have converted them to actual dates in my local format)

    3. As an example, how do you (manually) get 5 for the first value in column X? (If I apply your formula from post 1 to row 2, it returns #N/A for me)

    If I don't have the correct layout and/or the results for this sample data are incorrect, or you could demonstrate better with another small set of dummy sample data, please do so - preferably with one of the HTML Makers!

    Excel 2016 (Windows) 32 bit
    A B P Q R S T U V W X
    1 DB Nummer Prozess ISP Date Cluster Rollout-Project G-Strecken Lines Funnel-Status (FS) most recent entry Project ID
    2 1 05/02/2016 Paris 1 5
    3 2 05/10/2016 Rome # 2
    4 3 23/02/2017 New York 1 7
    5 4 01/02/2016 Toronto 1 4
    6 5 05/02/2015 Paris # 5
    7 6 05/10/2017 Rome 1 2
    8 7 23/02/2014 New York # 7
    9 8 01/02/2016 Toronto 1 4
    10 9 05/02/2016 Paris 1 5
    11 10 05/10/2016 Rome # 2
    12 11 23/02/2017 New York 1 7
    13 12 01/02/2016 Toronto 1 4
    Sheet6
    Hello Peter
    Since I'm a newcomer to MrExcel I have some difficulties with posting actuel files.
    To your questions:
    1. Yes the layout is correct.
    2. Yes I have real dates (format)
    3. The result is 5 which is correct. The array formula looks at column S in respect to the dates (Q) and takes the oldest date. Then it takes the number in column A.
    In this example it is Paris with the oldest date 05/02/2015.
    In Column A I use the following formula; =if(S2<>"";N(A1)+1;"")
    The array formula has to be entered with; ctrl-shift-enter
    I hope this helps.
    M
    Last edited by Peter_SSs; Sep 22nd, 2017 at 10:58 PM. Reason: Added Quote tag

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    36,422
    Post Thanks / Like
    Mentioned
    30 Post(s)
    Tagged
    6 Thread(s)

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

    Quote Originally Posted by MSchädler View Post
    Since I'm a newcomer to MrExcel I have some difficulties with posting actuel files.
    I am trying to help with that too.
    Which of the HTML Makers did you try, and what went wrong?



    Quote Originally Posted by MSchädler View Post
    3. The result is 5 which is correct. The array formula looks at column S in respect to the dates (Q) and takes the oldest date. Then it takes the number in column A.
    OK, got it at last.

    Try this in a copy of your workbook.

    Code:
    Sub ProjectID()
      Dim d As Object
      Dim a As Variant, aRws As Variant, aCols As Variant, b As Variant
      Dim lr As Long, r As Long
      
      Set d = CreateObject("Scripting.Dictionary")
      d.CompareMode = 1
      lr = Cells(Rows.Count, "Q").End(xlUp).Row
      aRws = Evaluate("row(2:" & lr & ")")
      aCols = Array(1, 17, 19) '<-Columns A, Q & S containing the 'data of interest'
      a = Application.Index(Cells, aRws, aCols)
      ReDim b(1 To UBound(a), 1 To 1)
      For r = 1 To UBound(a)
        If d.exists(a(r, 3)) Then
          If Split(d(a(r, 3)))(0) > a(r, 2) Then d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
        Else
          d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
        End If
      Next r
      For r = 1 To UBound(a)
        b(r, 1) = Split(d(a(r, 3)))(1)
      Next r
      Range("X2").Resize(UBound(b)).Value = b
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - 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

  10. #10
    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 Peter
    Thanks a lot for your much appreciated input.
    I will test this vba it in my sheet tomorrow try to understand the codes and give you a feedback.
    In the meantime enjoy your day and thanks again.
    Marc

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
  •  

 

 
DMCA.com