PDA

View Full Version : Help With MsgBox if duplicate range



MBESSANT
07-31-2008, 12:47 AM
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
07-31-2008, 02:03 AM
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.


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

Bob Phillips
07-31-2008, 02:19 AM
Can you post a workbook?

anisurrahman
07-31-2008, 02:50 AM
I think I already posted the code.. I hope it will work

MBESSANT
07-31-2008, 03:05 AM
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
07-31-2008, 03:19 AM
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
07-31-2008, 03:27 AM
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
07-31-2008, 03:30 AM
As I said, I don't think you can do it that way.

MBESSANT
08-01-2008, 02:28 AM
If I change CSV to a standard workbook, will it work then?

Thanks for your time

Bob Phillips
08-01-2008, 03:11 AM
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
08-01-2008, 01:46 PM
Does anybody know about Clone Remover? It's said to be a good duplicate file removal tool. moleskinsoft.com