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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.