If Value Exists on another worksheet, show error message

**BreatGritain** · 05-28-2014, 11:16 AM

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.

**nathansav** · 05-28-2014, 11:21 AM

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

public function BarcodeDupe(strBarcode as string) as boolean

BarcodeDupe =false

on error goto eHandle

if application.worksheetfunction.match(strBarcode ,worksheets("Stock List").range("B:B")>0 then

    BarcodeDupe =true

end if

exit function
eHandle:

end function

in your macro then, the first line woudl be

if BarcodeDupe(range("d7")) then msgbox("DUPLICATE")

UNTESTED

**AlphaFrog** · 05-28-2014, 11:39 AM

Originally Posted by BreatGritain

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

**BreatGritain** · 05-28-2014, 12:13 PM

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

=COUNTIF('Stock List'!$B:$B,$D7)>0

And threw that in cell A40, well out of the way.

Then on my macro I did this:

If Range("A40") = "True" Then
MsgBox "Barcode already exists"

Else
(Rest of code)
End If

Really apreciate the help, thank you.

**AlphaFrog** · 05-28-2014, 12:23 PM

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

If Application.CountIf(Sheets("Stock List").Range("B:B"), Sheets("New Stock").Range("D7")) > 0 Then
    MsgBox "Barcode already exists. ", vbExclamation, "Invalid Entry"
Else
    '(Rest of code)
End If

**BreatGritain** · 05-28-2014, 12:39 PM

You're bloomin' good you.

Thank you

If Value Exists on another worksheet, show error message

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If Value Exists on another worksheet, show error message

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Error Worksheet Exists - need code to continue to next name if it does

[SOLVED] Compile Error while checking if worksheet exists....

Function to show if a new error record exists

Run the Macro if cell value is YES, else show error message.

Trying to determine if worksheet exists, subscript out of range error

Bookmarks

Bookmarks

Posting Permissions