Consulting

Results 1 to 2 of 2

Thread: Copy & paste test

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,838
    Location

    Copy & paste test

    Preventing duplicate entries within a range.

    Here is a technique to prevent duplicate entries within a range (which is easy to do if you're entering, let's say, invoice numbers).

    Suppose you want to prevent duplicate entries in the range A1:A10:
    1. Select cell A1 and choose the Validation command from the Data menu.

    2. Click the Data Validation dialog's Settings tab.
    3. In the Allow dropdown list, select Custom.
    4. In the Formula box, type:.

      =NOT(OR(COUNTIF($A$1:$A$10,A1)>1))
    5. Now you'll need to set an alert style. Click the Error Alert tab.
    6. Set the Style dropdown to Stop (the alert message with a 'Stop' sign on it).
    7. In the Title box, type what you want to appear in the Error Alert's title bar (for example, "Duplicate Entry").
    8. In the Error message box, type the your text (for example, "This value already exists in the list").


    9. Click OK.
    10. Finally, back in the worksheet, copy cell A1 down through A10.
    Note: This material copyright of Rodney Powell Microsoft MVP - Excel, and I remain indebted to him for its use in this test
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,838
    Location
    The purpose of the above post, was to see if examples could be posted by using the cut & paste method of examples created and stored, dealing with issues raised in these forums, where a logical sequence is laid out for all to follow. Its a concept that I wish to explore in greater detail.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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