check if serial number is repeated

[AhmedMatboly]

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]

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.

[snb]

Sub M_snb()
if not iserror(application.match(ComboBox4,ThisWorkbook.Worksheets(3).Range("a1:a1000"),0)) then MsgBox "pls choose valid SN"
End Sub

[SamT]

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

[snb]

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]

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]

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

[SamT]

Code name is best