Detecting duplicates in whole column by VBA

Greetings from Latvia, and i hope, ill find a good solution or any useful help in this forum.


At the moment im making a small project in VBA Excel for creating bills and invoices, so this is where my storry begins.
Thefore, Sheet1 in my excel workbook is a sheet, where the serial numbers and names of invoices and bills are stored.
Numbers are taken from Sheet2 from a specific cell, where a user must enter a name for invoice or bill. There are no specific terms by entering name (no criterias), it can be as simple as INV-01 or BILL- 22 or something like that

So i created a macro, that works like this-

when the button is pressed, macro takes 3 or more values from Sheet2 cells and stores value of these cells in Sheet1 in specified cells
No problems so far.

What i want to do -

I want to check entire Sheet1 column A for duplicates so the user cannot enter the same number of invoice twice in Sheet2 (actually it will be possible but it wont go further to store these values in Sheet1)

could probably look like:

Sub Duplicate check ()

.... ..... ....


If ..... then Duplicates found Else Continue

End Sub



'i will create a modules named "duplicates found" wich will pop out a Msgbox "Duplicates found" and reset a cell value of invoice number in Sheet2 and no values will be stored in Sheet1 and a second module Continue (to proceed if there are no duplicates) (no problems to make this)

The problem is, i have no idea, what is the code for checking duplicate values (it may sound dumb, cause i've done much more complicated things in this project already)

Any ideas?

P.s. sorry for my English

Try

Please Login or Register  to view this content.

Nope, there's a bug in it (or a bug in my brains )


At first - shouldn't the beginning be like by the proper code rules:

Dim CheckVal As Integer
Dim MyValue As Integer



Oh, if i get it right, the Option Explicit already does it?
____________________________

If i try to run code, it says - Compile error End If without block If
If i remove line End if then code always does the Else (Continue) macro no matter what values are in Sheet1 column A

Why not to place a code for worksheet change event on sheet2 where user enters invoice numbers and let all other codes remain same, so that if user re-enters an invoice number, user will get a message that you have entered a duplicate value and will be forced to change it.
You may place this code on Sheet2 (not on a module) by clicking on Sheet2 Tab --> View Code --> and paste the code given below in the code window.

Please Login or Register  to view this content.

Does this help?

First, I do see why the previous code sent got an error ...
Now, try next one and see in the file attached how it's running.
For more information send a short sample of your data

Please Login or Register  to view this content.

Phew, looks like its going to reach final destination, but there is a one last problem left

Please Login or Register  to view this content.

I need to delete row values in case if CheckVal >= 2

Please Login or Register  to view this content.

this doesnt work

the root of evil is, that invoice numbers couldnt be entered manually in Sheet1 (its the order of customer), for myself, i use this project for making bills for my own as it was in beginning when i just started to make it, no automatically saved numbers, no warnings and so on. Then once i showed this to a friend of mine, and he said - wow, i need this, lets make a good deal, but i need just few more things like .... ...

Is it what you want ??

Please Login or Register  to view this content.

I'm not sure to get it right.
In the code I sent there is a Select statement and
when CheckVal is 0 then there is no duplicate
else if equal 1 there is a duplicate to come with the new value
and else (= 2, 3 ..) it search for the first value and delete row.
Here again send a sample of your file
You have to use the FIND statement to know where is the first duplicate

Problem solved - figured it out my way, as i said, im not a proggrammer and its hard to explain my needs in foreign language and by just describing them, phew.

So -

Please Login or Register  to view this content.

and here is

Please Login or Register  to view this content.

And thats all i wanted to create

Short explanation -

A radio button transfers 3 values of Sheet3 (Invoice) cells to sheet1 (Number info storage) cells (inv. number, date, customer) and creates a pdf file in a specific directory on hard drive.

When the values are transfered, sub cheks, if there any duplicates when you hit a radio button in Sheet3 again - so the procedure runs correctly at next data transfer, (when you hit a radio button to copy new data from sheet3 to Sheet1)

If there is no duplicates then MsgBox "Invoice saved" and save as pdf code in another module runs
If there any, they're beeing deleted on fly and user gets a warning and is forced to change Invoice number to much criteria (no duplicates)


Thanks a lot guys, without your help i would never dig throught this

I'm please it's working
Just to close the loop try

Please Login or Register  to view this content.

Just use

Please Login or Register  to view this content.

and MyValue is Variant and can be : Number, text

Nope, if i use Dim MyValue
it allows to invoice number look like INV1 but the criteria doesnt work - it doesnt count duplicate values (no errors) and the sub deleterows doesnt work

Because strings are used there is an adjustment to do when counting cells
Use next code, pay attention to the number of "

Please Login or Register  to view this content.

Good news.
You're welcome

You have better to open a new thread to restart with a new topic, else people will miss you.