View Full Version : check if serial number is repeated
AhmedMatboly
10-10-2022, 06:03 AM
i hope to know why that code not running and serial numbers repeat without exit sub
Dim sernum As Range
Dim i As Range
Set sernum = ThisWorkbook.Worksheets(3).Range("a1:a1000")
For Each i In sernum
If ComboBox4.Value = i.Value Then
MsgBox "pls choose valid SN"
Exit Sub
End If
Next i
p45cal
10-10-2022, 11:32 AM
Where is the code?
What's the sub called (named)(The very first line of the sub)?
From this we can try and find out what, if anything, is trigering the code to run.
Best attach a workbook with the bare minimum in to demonstrate the problem.
Sub M_snb()
if not iserror(application.match(ComboBox4,ThisWorkbook.Worksheets(3).Range("a1:a1000"),0)) then MsgBox "pls choose valid SN"
End Sub
Function GoodSerno(CheckSerno as String) As Boolean
Dim Found As Range
Set Found = Sheets("Sheet3").Range("A:A").Find(CheckSerno) 'verify sheet Name
GoodSerno = Found Is Nothing
End Function
Usage in some sub
If GoodSerno("AB3456") then
NewSerno = "AB3456"
Else Try Again
End If
Note that ThisWorkbook.Worksheets(3) can change sheets
More robust:
Sub M_snb()
If Not IsError(Application.Match(ComboBox4, Sheet2.Columns(1), 0)) Then MsgBox "pls choose valid SN"
End Sub
or
Sub M_snb()
If Not Sheet2.Columns(1).Find(ComboBox4) Is Nothing Then MsgBox "pls choose valid SN"
End Sub
AhmedMatboly
10-18-2022, 05:03 PM
More robust:
Sub M_snb()
If Not IsError(Application.Match(ComboBox4, Sheet2.Columns(1), 0)) Then MsgBox "pls choose valid SN"
End Sub
or
Sub M_snb()
If Not Sheet2.Columns(1).Find(ComboBox4) Is Nothing Then MsgBox "pls choose valid SN"
End Sub
Thnx snb i will try your code seems its hard coded really appreciate it
AhmedMatboly
10-18-2022, 05:05 PM
Thnx samt
U mean if i change sheets arrange code will apply in another sheet i guess
I was starting learn vba when i post this thread
Now i use code name
This is best way to refer to sheets or not from your point of view as expert
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.