+ Reply to Thread
Results 1 to 13 of 13

repeated article in 3 sheet

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

    repeated article in 3 sheet

    I to all.
    This macro
    Please Login or Register  to view this content.
    works on 1 sheet only.
    You can change it to work on all sheets of the workbook.
    data_base_1
    data_base_2
    data_base_3
    Thanks you
    john
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: repeated article in 3 sheet

    Hi there,

    Take a look at the attached version of your workbook and see if it does what you need. It uses the following code:

    Please Login or Register  to view this content.
    Nota: il sistema qui non mi ha permesso usare la lettera con accento

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

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

    Re: repeated article in 3 sheet

    Hi greg
    the macro must work for all 3 sheets if the value is repeated.
    Example:
    if repeated in the data_base_1 sheet and insert the same value in the data_base_3 sheet, a warning must appear:

    MsgBox Prompt:="Attenzione: codice " & _
    "gia presente nella riga " & i + 1, data_base_1
    Buttons:=vbCritical, _
    Title:="Doppione!" ' <<< 2 = parte dalla riga 1

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: repeated article in 3 sheet

    Hi again,

    Thanks for your feedback.

    See if the following code does what you need:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your requirements.


    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

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

    Re: repeated article in 3 sheet

    I have tried several times to insert this value 123456 as an example in all 3 sheets but no warning appears

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: repeated article in 3 sheet

    Hi again,

    I think that the situation is possibly not helped by the fact that the "numeric" values in Column A are in fact formatted as text values. I'm not sure, but this might have caused duplicate values to be ignored. I have changed the formatting to "General" and the code seems to function correctly for me.

    In the attached version of your workbook, the value 123456 is entered on Row 3 of worksheet "data_base_1", on Rows 4 & 5 of worksheet "data_base_2", and on Rows 6, 7 & 8 of worksheet "data_base_3". Row 2 of worksheet "data_base_1" is blank.

    When I enter a value of 123456 in Cell A2 of worksheet "data_base_1" I receive six separate warning messages - one for worksheet "data_base_1", two for worksheet "data_base_2", and three for worksheet "data_base_3".


    Hope this helps - as before, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

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

    Re: repeated article in 3 sheet

    Hi greg
    the cell format of the cells must remain text.
    I have tried several times both text and general but not no warning appears

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: repeated article in 3 sheet

    What can I say?

    In the attached workbook I have changed the formatting of the cells in Column A on each of the three worksheets to "Text"

    When I enter 123456 into the empty Cell A2 of worksheet "data_base_1" I get six separate messages telling me that:

    the value 123456 exists already on Row 3 of worksheet "data_base_1"

    the value 123456 exists already on Row 4 of worksheet "data_base_2"
    the value 123456 exists already on Row 5 of worksheet "data_base_2"

    the value 123456 exists already on Row 6 of worksheet "data_base_3"
    the value 123456 exists already on Row 7 of worksheet "data_base_3"
    the value 123456 exists already on Row 8 of worksheet "data_base_3"


    I will assume that you have Events enabled for the workbook

    It works on my system - I don't know why it doesn't work on yours

    Regards,

    Greg M
    Attached Files Attached Files

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

    Re: repeated article in 3 sheet

    Hi greg, I don't know what to say
    If I insert here at home with excel2007 but also in the office with excel365 in A2 sheet data_base_1 value 123456 no warning appears

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: repeated article in 3 sheet

    Hi again,

    VERY strange!

    Can you set a break point at the start of the "CheckData" routine and step through it to see what happens when you insert 123456 in Cell A2?

    Regards,

    Greg M

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

    Re: repeated article in 3 sheet

    Hi greg,
    I don't know how to do with the procedure you ask me to do

  12. #12
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: repeated article in 3 sheet

    Hi again,

    Open the VBA Editor and place the cursor somewhere in the line:

    Please Login or Register  to view this content.
    Now press the F9 key - the line will be highlighted and a coloured (brown?) spot will appear in the left margin.

    Return to the main Excel window and enter 123456 in Cell A2 of the "data_base_1" worksheet

    The display will "jump" to the above line of code and will pause ("break") waiting for your input.

    Pressing the F8 key will cause the code execution to jump to the next executable statement.

    By using the F8 key to step through the code it should be possible to see what the code is/is not doing.

    If you want to finish stepping through the code and allow it to continue uninterrupted until the end, just press the F5 key.


    Regards,

    Greg M

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

    Re: repeated article in 3 sheet

    Hi greg.
    Breakpoint inserted as per attached photo
    inserted in sheet data_base_3 value 123456
    returned to vba and pressed F9
    stops in the brown (third) row with the yellow arrow and does not continue
    Attached Images Attached Images

+ 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. [SOLVED] Repeated words: remove duplicates, display word along with the number of times repeated
    By number9 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-24-2019, 03:32 PM
  2. [SOLVED] Counting the article
    By sathishkm in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-30-2013, 02:30 PM
  3. [SOLVED] Repeated action - shorten macro - copy from each sheet to summary sheet
    By sans in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-07-2012, 11:37 AM
  4. [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
  5. [SOLVED] Looking for a MSDN article.
    By Zoo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2006, 05:25 AM
  6. [SOLVED] Article creation
    By excelisfun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2006, 09:20 AM
  7. [SOLVED] Printing text in a repeated cell/row that is longer than repeated
    By Valerie Dyet in forum Excel General
    Replies: 1
    Last Post: 02-12-2006, 11:30 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