+ Reply to Thread
Results 1 to 8 of 8

If value is not in range: error message, then check if duplicates

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Norway
    MS-Off Ver
    10
    Posts
    43

    Question If value is not in range: error message, then check if duplicates

    Hello,

    I currently have the code below, and it checks if there are duplicates of the entered value on all sheets. I would also like to prevent users from entering a value that is not within a certain range. How to add this to my code? I've tried to add another If, but it just ended up looping.

    Maybe it would be good to make something like:
    1. first check if value is within range (between 1000000 and 3999999). if not within range: msgbox and delete entry.
    2. then check for duplicates

    Here is the code I have so far:



    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: If value is not in range: error message, then check if duplicates

    Hi vonrainer,

    Try the following. Changes are in red. I tested it the best that I could on my dummy worksheet.
    Please Login or Register  to view this content.
    Please take a look at the following code and make sure it works the way you want. When I cut and pasted several values into the range only the first item was deleted. The MsgBox message does not seem to match the test (testing for more than one cell being changed).
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    Norway
    MS-Off Ver
    10
    Posts
    43

    Re: If value is not in range: error message, then check if duplicates

    Thank you for your reply. The copy/paste with several values will not be an issue, as all values will be entered manually.

    The modified code gives me an error: Compile Error: Variable not Found
    This error is on the marked line
    Please Login or Register  to view this content.
    Any help?

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: If value is not in range: error message, then check if duplicates

    Hi, vonrainer,

    Dim the variable and try again?

    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    06-26-2014
    Location
    Norway
    MS-Off Ver
    10
    Posts
    43

    Re: If value is not in range: error message, then check if duplicates

    Thanks guys! Now workes perfectly I owe you one

  6. #6
    Registered User
    Join Date
    06-26-2014
    Location
    Norway
    MS-Off Ver
    10
    Posts
    43

    Re: If value is not in range: error message, then check if duplicates

    Well, I was a bit quick to mark as solved. There's only one small bug. The new function to check if entered value is within range applies to all cells in the worksheet, however I would like it to only check the entered value in cells the range "A1:A60", as it does for the duplicate-checker.

    The code is now as posted below. How can I make the marked code work only for values entered in the range "A1:A60", as set by the the SetEvalRange is at the top?
    Please Login or Register  to view this content.

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: If value is not in range: error message, then check if duplicates

    Hi, vonrainer,

    make use of the range you have built with EvalRange by checking the target to this range, and I would make sure that the procedure will only be started if one cell in the target area will be changed or use a loop to run through the other cells as well:

    Please Login or Register  to view this content.
    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    06-26-2014
    Location
    Norway
    MS-Off Ver
    10
    Posts
    43

    Re: If value is not in range: error message, then check if duplicates

    Thanks, worked like a charm

+ 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] Error checking routine - check range values on various worksheets and report if error
    By TheRobsterUK in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-29-2014, 06:36 AM
  2. Replies: 0
    Last Post: 07-31-2013, 06:03 AM
  3. Replies: 1
    Last Post: 12-14-2012, 10:11 AM
  4. Replies: 2
    Last Post: 12-18-2009, 05:20 PM
  5. Check list for duplicates and display message
    By vote4pedro in forum Excel General
    Replies: 9
    Last Post: 09-01-2008, 08:39 AM

Tags for this Thread

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