+ Reply to Thread
Results 1 to 31 of 31

macro repeated values

  1. #1
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    macro repeated values

    Hi to all.
    This macro reports repeated values:

    Please Login or Register  to view this content.

    reports repeated values
    Is it possible to change it?
    It must start from row 5
    if more than one repeated value needs to appear in the msgbox, it is now for one value only
    Thanks you
    john

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Oops the file did not attach, here it is
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    if more than one repeated value needs to appear in the msgbox, it is now for one value only
    Could you please explain in more detail what you mean by this? Use a few examples from your data.

  5. #5
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    If in my file I add in column A cell 15 example "A1" should appear in the msgbox

    MsgBox "Attention!" & Chr (10) & _
    "code already inserted in rows " & c.Row & Chr (10) & Chr (10) & "", vbInformation, "NOTICE!"

    code already inserted in row = 7/8/9/15

  6. #6
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    I correct the macro:

    Please Login or Register  to view this content.
    the 50000-rows macro is very slow.
    you can also insert the message in the cell E2

    you can also edit the message in the cell E2
    Attached Files Attached Files

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    Try:
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Thanks munps1
    the only problem is that it is very slow if there are many rows

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    How many rows do you have and how long does the macro take to execute? Do you have formulas or conditional formatting in your worksheet?

  10. #10
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Now with the tests there are 15,000 lines and the time is more or less 5 seconds, but then the lines will be many more, even 50000.

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    See if this makes a difference:
    Please Login or Register  to view this content.

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: macro repeated values

    I don't know if this faster.
    Please try


    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi bo_ry,
    i tried with 50000 rows and it's very fast, thanks.
    A small problem in the cell message Range("E2")=mMsg
    the number 7 is always repeated

    ----------------------------------------------------------

    Hi mumps1
    i tried with 50000 rows and it's very fast, thanks.
    A small problem when a value is entered for the first time
    message appears: MsgBox "Attenzione!" & Chr(10) & "codice già inserito nella riga "

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: macro repeated values

    Fix show number 6

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-30-2020 at 04:59 PM. Reason: Fix show number 6

  15. #15
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Bo_ry now the ever present number is 6

  16. #16
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    Try:
    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi bo_ry your modification is almost exact.
    A small mistake if I enter a new value in cell A7
    this value is seen in cell E3 but not in the msgbox

    ---------------------------------------------------------------------------------------------

    Hi munps1 your edit is fine.
    Is it possible not to display the "\" symbol in E3 and msgbox after the last entered value?


    Thanks to all

  18. #18
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: macro repeated values

    This should work

    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi bo_ry your edit now work well, thanks

  20. #20
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: macro repeated values

    Try:
    Please Login or Register  to view this content.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: macro repeated values

    See if this is faster.
    Please Login or Register  to view this content.
    Last edited by jindon; 12-01-2020 at 10:51 AM.

  22. #22
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Mumps1 your macro now works, thanks

  23. #23
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi jindon
    i tried your macro in my workbook but it works partially.
    Each time a new value is entered, the message is displayed.
    In all entries of other values in the msgbox, the number of rows is not displayed
    if the value is only number in the msg no rows are displayed
    Attached Files Attached Files
    Last edited by john_cash; 12-01-2020 at 03:06 PM.

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: macro repeated values

    OK, but I see the row that I enter a new value in new row...
    BTW cell format in E2 should be String, otherwise it sometime confuses as a DATE.
    Please Login or Register  to view this content.
    Last edited by jindon; 12-02-2020 at 04:13 AM.

  25. #25
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi jindon
    i tried your macro but it works partially.
    At each new entry the warning must not appear
    in cell E2 every now and then the format is date
    Attached Files Attached Files

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: macro repeated values

    As I said, E2 should be formatted as TEXT.
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi jindon
    O.k. in cell E2 every now and then the format is date
    but At each new entry the warning must not appear

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: macro repeated values

    Are you saying when the entry is unique(no duplicates), no mesgbox?
    When duplicates, exclude the newly entered row?

    Say, you have A1A in A8 & A9.
    If you enter A1A in A10, what should be in msgbox?

    8/9 or 8/9/10?

  29. #29
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Are you saying when the entry is unique(no duplicates), no mesgbox? = yes this

    Say, you have A1A in A8 & A9.
    If you enter A1A in A10, what should be in msgbox? = 8/9/10? yes this

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,463

    Re: macro repeated values

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    10-31-2018
    Location
    venezia
    MS-Off Ver
    2007
    Posts
    481

    Re: macro repeated values

    Hi jindon now it's o.k., thanks
    john

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to Insert 4 Columns from Column E and fill repeated values in inserted columns
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2019, 10:18 PM
  2. Repeated tasks with different values to be run with Macro
    By antony50910 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2015, 06:18 AM
  3. Replies: 1
    Last Post: 10-11-2014, 04:52 AM
  4. [SOLVED] Data extract with macro on multiple repeated values
    By sats43 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-25-2014, 04:56 AM
  5. [SOLVED] Append data to repeated values with a fixed array to be repeated on value change
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-06-2011, 12:30 AM
  6. Macro for Repeated Values
    By sagar in forum Excel General
    Replies: 1
    Last Post: 01-21-2009, 09:05 AM
  7. Macro for Repeated Values from Multiple Columns
    By deaerator in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-17-2008, 01:49 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1