+ Reply to Thread
Results 1 to 6 of 6

If Value Exists on another worksheet, show error message

  1. #1
    Registered User
    Join Date
    12-10-2013
    Location
    West Yorkshire, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    If Value Exists on another worksheet, show error message

    Hi.

    I use an excel spreadsheet to track my stock, but i'm after a code to prevent me from having two records with the same barcode.

    When adding new stock to the spreadsheet using a Worksheet called "New Stock", I type the barcode into cell D7 (along with other info about the stock in other cells on that workbook)

    I then hit a macro which copies the information to a worksheet called "Stock List" (using vlookup to find the first empty row to paste it to)

    I'm wondering if there is any way I can get excel to check whether the barcode I enter into D7/New Stock is already somewhere in column B/Stock List

    Either by modifying the current macro which copies the info across, or by conditional formatting where say cell D8 turns red and tells me i'm an idiot.

    Anyone have any ideas?

    Thank you very much for the help.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: If Value Exists on another worksheet, show error message

    Use the match function,

    application.worksheetfunction.match, its the same layout as in workbook formula, if it errors its ok, if it doesnt error then theres a match


    Please Login or Register  to view this content.

    in your macro then, the first line woudl be

    Please Login or Register  to view this content.
    UNTESTED
    Last edited by nathansav; 05-28-2014 at 11:28 AM.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,308

    Re: If Value Exists on another worksheet, show error message

    Quote Originally Posted by BreatGritain View Post
    or by conditional formatting where say cell D8 turns red and tells me i'm an idiot.
    If you want the conditional format (CF) solution, use this as a CF formula

    =COUNTIF(MyRange,$D7)>0

    If you have Excel 2007 or earlier, create a named range for 'Stock List'!$B:$B and reference that named range in the CF formula (MyRange in this example)


    If you have Excel 2010 or later, you can reference another sheet's range directly within the CF formula.
    =COUNTIF('Stock List'!$B:$B,$D7)>0
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    12-10-2013
    Location
    West Yorkshire, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: If Value Exists on another worksheet, show error message

    Thank you for the help both of you.

    Nathansav, I couldn't get your code to work - I don't really have a clue what i'm doing, but thank you for trying with me.

    AlphaFrog, ideally I wanted to avoid using the CF idea because it isn't idiot proof, and as previously mentioned, I am an idiot.

    However, thanks to your help I managed to bodge something together.

    I used your code
    Please Login or Register  to view this content.
    And threw that in cell A40, well out of the way.

    Then on my macro I did this:

    Please Login or Register  to view this content.
    Really apreciate the help, thank you.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,308

    Re: If Value Exists on another worksheet, show error message

    Well done. You could eliminate the A40 formula like this...

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-10-2013
    Location
    West Yorkshire, UK
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: If Value Exists on another worksheet, show error message

    You're bloomin' good you.

    Thank you

+ 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 Worksheet Exists - need code to continue to next name if it does
    By missit in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2014, 12:10 AM
  2. [SOLVED] Compile Error while checking if worksheet exists....
    By tnuis in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-15-2013, 09:48 AM
  3. Function to show if a new error record exists
    By Supply_Analyst811 in forum Excel General
    Replies: 7
    Last Post: 12-08-2010, 03:37 PM
  4. Run the Macro if cell value is YES, else show error message.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2008, 02:03 AM
  5. Trying to determine if worksheet exists, subscript out of range error
    By js999 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2007, 12:03 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