Dublicate value alert with message

[computron]

Hi,

Please help me with below macro, details are given below

Column A Column B Column C Column D
Date Paid Amt Invoice No. Remarks
12/01/12 $5000.00 7865986 Paid on 12th Jan 2012
14/01/12 $4500.00 7866896 Not Paid


Now what i wanted is if i am entering any dublicate invoice number in column c, it should give popup alert & it should show the message that you have entered dublicate invoice number & details are given below.
it should show below information in that popup
Date, Paid Amt, Invoice No. & Remarks releated to that dublicate invoice number.

can u please help me with this, if this not possible then how can i do in alternet way.

thanks in advance.

regards,
Ritesh

 

[Andrew Poulsom]

Can't you use Data Validation?

http://www.contextures.com/xlDataVal07.html#Unique

 

[computron]

Data validation is not giving me dublicate invoice number details like paid amount & remarks.

please help thanks.

 

[Andrew Poulsom]

Are you happy with a VBA solution?

 

[computron]

Hi,

I have a excel 2010, when i am doing through data validation custom, its not working, its accepting all values, but when i am selecting other options instead of custom then data validation is working.

i dont know what's wrong, anything with formula?

waiting for your reply, give me any other macro solution for this.

regards,
Ritesh

 

[computron]

Hi,

Now validation is working but the problem is sometimes i am pasteing invoice number from another excel file. that time its accepting dublicate invoice number, how can i fix this.

regards,
Ritesh

 

[computron]

Please help!!! pasting values not accepting any validation rules.

how can we fix this....

please help!!!

 

[Muhamed Faizal]

You can use the following macro, you can edit as per your requirement.


Sub duplicate()
Dim lastrow As Long
lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
Value = ActiveCell.Value
For i = lastrow To 2 Step -1
For J = 2 To lastrow - 1 Step 1
If Cells(J, "C").Value = Cells(i, "C").Value And J <> i Then
MsgBox ("This invoice is already in the Database at row no:" & i)
End If
Next J
Next i
End Sub

 

[computron]

Thanks for your help.

 

[Muhamed Faizal]