How to add "Else Msgbox" To this piece of code?

[mageeg]

Hi, i have written a piece of code which checks whether a serial number is between two certain values. If it is then it pulls data from that row.

I would like to add an else messagebox that says ("Serial number not found") However i can't seem to do this without it coming up no matter what serial number i put in.

See code below:

Private Sub SerialNumber_AfterUpdate()
Dim ID As Long
Dim StartID As Long
Dim EndID As Long
Dim i As Range

If SerialNumber.Value = "" Then

Else

'code for receiver range, *if number entered is between range then lookup rest of data*


    ID = SerialNumber.Value
    
        For Each i In Sheets("ReceiverData").Range("Table2[From]")
        
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
        
            If ID >= StartID And ID < EndID Then

            txtType.Value = i.Offset(0, 3).Value
            txtSize.Value = i.Offset(0, 4).Value
            txtWKPRESS.Value = i.Offset(0, 6).Value
            txtCertDate.Value = i.Offset(0, 5).Value
            
            End If
        
        Next i

End If


End Sub

 

[Fluff]

How about

Private Sub SerialNumber_AfterUpdate()
Dim ID As Long
Dim StartID As Long
Dim EndID As Long
Dim i As Range
Dim Flg As Boolean

If SerialNumber.Value = "" Then

Else

'code for receiver range, *if number entered is between range then lookup rest of data*


    ID = SerialNumber.Value
    
        For Each i In Sheets("ReceiverData").Range("Table2[From]")
        
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
        
            If ID >= StartID And ID < EndID Then

            txtType.Value = i.Offset(0, 3).Value
            txtSize.Value = i.Offset(0, 4).Value
            txtWKPRESS.Value = i.Offset(0, 6).Value
            txtCertDate.Value = i.Offset(0, 5).Value
            Flg = True
            End If
        
        Next i

End If
If Flg = False Then MsgBox "not found"

End Sub

 

[mageeg]

How about

Private Sub SerialNumber_AfterUpdate()
Dim ID As Long
Dim StartID As Long
Dim EndID As Long
Dim i As Range
Dim Flg As Boolean

If SerialNumber.Value = "" Then

Else

'code for receiver range, *if number entered is between range then lookup rest of data*


    ID = SerialNumber.Value
   
        For Each i In Sheets("ReceiverData").Range("Table2[From]")
       
        StartID = i.Value
        EndID = i.Offset(0, 1).Value
       
            If ID >= StartID And ID < EndID Then

            txtType.Value = i.Offset(0, 3).Value
            txtSize.Value = i.Offset(0, 4).Value
            txtWKPRESS.Value = i.Offset(0, 6).Value
            txtCertDate.Value = i.Offset(0, 5).Value
            Flg = True
            End If
       
        Next i

End If
If Flg = False Then MsgBox "not found"

End Sub

That was the exact method i was trying to use but i was trying to put it in the wrong place. instead i was trying to implement it inside the already made IF. whoops!

Works perfectly, thank you!

 

[Fluff]

Glad to help & thanks for the feedback.