Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 30

Thread: VBA code (macro) to replace array formula

  1. #11
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,181
    Post Thanks / Like
    Mentioned
    75 Post(s)
    Tagged
    15 Thread(s)

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

    Quote Originally Posted by MSchädler View Post
    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
    OK, thanks. Interested to hear how it goes.
    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

  2. #12
    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
    Here is my first reply. I quickly made a excel file with the settings described above (columns A to X) and used the titles (DB Nummer to Project ID), in row 1.
    I then copied your vba code first into the workbook. Nothing happend.
    Then I took the command and paste it into table 1 which is the worksheet. Nothing happend.

    For some reason it does not start by itself. What do I do wrong?

    I then went to the step by step (F8) command in the debug mode. There it went to the line "If d.exists(a(r, 3)) Then" and is just looped to "next r" and back to If.d.exists...


    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


    Also nothing was written into column X.
    Do you have any idea what is wrong?
    Thanks for you help.
    M.

  3. #13
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,181
    Post Thanks / Like
    Mentioned
    75 Post(s)
    Tagged
    15 Thread(s)

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

    I didn't really understand your description of where you put the code. It should be in a standard Module:
    With the worksheet active press Alt+F11 to bring up the vba window, use Insert -> Module and paste the code in the right hand pane that opens. Remove any other instances of the code in the workbook.

    There was no mention of the code starting by itself. You need to run it when you want column X updated. One way is, from the worksheet, Alt+F8 to bring up the Macro dialog, Select the macro & click ‘Run’.

    You described adding the headings but can you confirm that you also added the sample data as shown in A2:W13 in post #7 ?

    Did you alter the at all? If so, please post the actual code you used.
    Last edited by Peter_SSs; Sep 23rd, 2017 at 05:38 AM.
    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

  4. #14
    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
    With your clarification and using Alt+F8 + enter I do get digits in column x.
    Now we just have to find out, why the result is not as expected.

    Here is the setup (sheet) that I'm using (not changed since post#7):

    Col A =
    DB Nummer
    Col P ISP Col Q
    Date
    Col R Cluster Col S
    Rollout-Project
    Col T
    G-Strecken
    Col U
    Lines
    Col V
    Funnel-Status (FS)
    Col W
    most recent entry
    Col X
    Project ID
    (returned with code)
    I did
    expect
    1 05.02.2016 Paris 1 9 5
    2 05.10.2016 Rome # 10 2
    3 23.02.2017 New York 1 11 7
    4 01.02.2016 Toronto 1 12 4
    5 05.02.2015 Paris # 9 5
    6 05.10.2017 Rome 1 10 2
    7 23.04.2014 New York # 11 7
    8 01.02.2016 Toronto 1 12 4
    9 05.02.2016 Paris 1 9 5
    10 05.10.2016 Rome # 10 2
    11 23.02.2017 New York 1 11 7
    12 01.02.2016 Toronto 1 12 4

    Logic = expected results:
    In Col A Paris is on Nummer 1 and 5 and 9. Paris with the "oldest" date (05/02/2015) is in Col A DB Nummer 5. Therefore all Rollout Project = Paris must get 5 in Col X.
    In Col A Rome is on Nummer 2 and 6 and 10. Rome with the "oldest" date (05/10/2016) is in Col A DB Nummer 2. Therefore all Rollout Project = Rome must get 2 in Col X.
    The logic is build this way.

    I hope you can help me find why the result with the macro is not like expected --> see last Column.
    Marc

  5. #15
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,181
    Post Thanks / Like
    Mentioned
    75 Post(s)
    Tagged
    15 Thread(s)

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

    I'm getting the red expected results. So clearly something is different between your sheet & mine.
    Can you upload your sample workbook, with the code included, to a public file-share site (eg DropBox) and post a link here?
    Last edited by Peter_SSs; Sep 23rd, 2017 at 07:45 AM.
    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. #16
    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

    Here is the link to the drop box: https://www.dropbox.com/s/44zz7xlkzk...rray.xlsm?dl=0
    Its my Test Excel with the code.

    I hope it works. It's the first time I use this - normally I send by Mail.
    Marc
    Last edited by MSchädler; Sep 23rd, 2017 at 08:48 AM. Reason: wrong link

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,181
    Post Thanks / Like
    Mentioned
    75 Post(s)
    Tagged
    15 Thread(s)

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

    Quote Originally Posted by MSchädler View Post
    I hope it works.
    Yes, it worked, thanks. There are two issues:

    1. You don't have the code in the correct place per my instructions in post #13. When you are in the vba window looking at the code, use the vba menu to Insert -> Module
    That should create 'Module1' in a 'Modules' folder below the two items you have in the 'Microsoft Excel Objects' folder. Cut and Paste the code from the 'DieseArbeitsmappe' (ThisWorkbook) module into 'Module1'

    2. There is an issue with the dates in your file that behave differently to my file and I am not exactly sure why. In any case make this change in the code & try it again.
    Code:
    If CLng(Split(d(a(r, 3)))(0)) > a(r, 2) Then d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
    Last edited by Peter_SSs; Sep 23rd, 2017 at 09:21 AM.
    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

  8. #18
    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
    Thanks for your patience. I now understanding thatmy English knowledge with excel is limited.
    Anyway, I did create a module1 in the sheet, inserted the code, made the correction you gave me and
    ā it worked.EUREKA.
    Is there a way to automate the vba command, when you enter a new "Rollout-Project" in column S?
    Marc

  9. #19
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,181
    Post Thanks / Like
    Mentioned
    75 Post(s)
    Tagged
    15 Thread(s)

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

    Quote Originally Posted by MSchädler View Post
    Anyway, I did create a module1 in the sheet, inserted the code, made the correction you gave me and ā it worked.EUREKA.
    At last!

    Quote Originally Posted by MSchädler View Post
    Is there a way to automate the vba command, when you enter a new "Rollout-Project" in column S?
    Try this
    1. Right click the 'Test' sheet name tab and choose "View Code".
    2. Copy and Paste the code below into the main right hand pane that opens at step 1.
    3. Close the Visual Basic window & test.


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Columns("S")) Is Nothing Then
        With Application
          .ScreenUpdating = False
          .EnableEvents = False
          ProjectID
          .EnableEvents = True
          .ScreenUpdating = True
        End With
      End If
    End Sub
    Last edited by Peter_SSs; Sep 23rd, 2017 at 10:16 AM.
    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

  10. #20
    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
    Thanks again. Now it works the way I wanted it thanks to you.
    I now close this case and wish you a wonderful day or evening or both.

    Kind regards
    Marc

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
  •