Help With MsgBox if duplicate range

[MBESSANT]

Hi,

I am trying to add some VB code to a excel file. It is used as a daily log of all the boats going in to the storage yard.

Every boat has a individual serial number, but sometimes the serial is duplicated in error. I have already added some code to export the serial numbers to a CSV file, but I cant get my head round checking in the CSV file to see if the serial is already used.

Anyone got any code I can put in?

Many thanks

[anisurrahman]

Hi,

I am trying to add some VB code to a excel file. It is used as a daily log of all the boats going in to the storage yard.

Every boat has a individual serial number, but sometimes the serial is duplicated in error. I have already added some code to export the serial numbers to a CSV file, but I cant get my head round checking in the CSV file to see if the serial is already used.

Anyone got any code I can put in?

Many thanks

hi to look for a duplicate Serial- I assume you are using UserForm to input your Data - I hope following Code will solve your Problem.

[vba]
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet


With UserForm1
'CHANGE UserForm1 as per your UserForm name

Set ws = Worksheets("Sheet1")
' change as per your worksheet name

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'find first empty row in database

If WorksheetFunction.CountIf(ws.Range("A1", ws.Cells(iRow, 1)), Me.TextBox1.Value) > 0 Then

'TextBox1 is where you input your Serial Number

MsgBox TextBox1.Value & " SERIAL Already Exist", vbCritical
Exit Sub
End If


'=======================
'ENTER YOUR CODE HERE
'==========================

End with

End Sub

[/vba]

[Bob Phillips]

Can you post a workbook?

[anisurrahman]

I think I already posted the code.. I hope it will work

[MBESSANT]

Im not using a UserForm. Thanks for the code, I can use it in something else.

I have posted the blank demo file, this is just what im messing around with. I want the code to check the CSV log file before it saves. using the saveas button.

Thanks for your help

Martyn

[Bob Phillips]

Not absolutely clear. I ran the code and it created an empty file.

Are you trying to append to a file and want o check for duplicates there? If so, this approch won't wrk. Append appends, no questions asked. If you open the CSV file in Excel, you can process it as a standard workbook, incuding Find etc., then save it as a CSV file.

[MBESSANT]

Are you trying to append to a file and want o check for duplicates there?

I am trying to search the serial.csv for a duplicate serial before it appends the file with the latest serial update.

Thanks for your help


Martyn

[Bob Phillips]

As I said, I don't think you can do it that way.

[MBESSANT]

If I change CSV to a standard workbook, will it work then?

Thanks for your time

[Bob Phillips]

No, that is not what I am suggesting. Keep it as a CSV file, but open the csv file in excel, process it, then write it out again, not using the Print statements. When opened in Excel, a CSV file looks like a workbook, but you cannot save any formatting, VBA etc., which you don't need.

[Cleaner007]

Does anybody know about Clone Remover? It's said to be a good duplicate file removal tool. moleskinsoft.com