Thread: Search VBA causing error when data isn't there to find

Hi,

I'm creating some code that basiclly checks to see if a code is there before proceeding with the rest of the script. I can make it find existing codes and tell me that it's there but if I enter one that isn't there it freaks out and goes into debugging.

I need a way of stopping it from chucking a fit when the code doesn't already exist.

My current code is as follow;

Code:

Sub ChkJobNo()
Dim rval As Integer
'Number for Validating'
Dim strJobNo As String


'Checks the numbers in list'
    If Cells(1, 9) = 1 Then
        strJobNo = Cells(6, 2)



' Locates the Job No which is entered into the text box, used for the creation of new forms when there is already job details'
strJobNo = Cells(6, 2)
Sheets("Core").Select
Cells.find(What:=strJobNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate


If strJobNo = strJobNo Then MsgBox ("Job Number already exists!")
Sheets("Main").Select
End
If strJobNo = "" Then
Exit Sub

End If



End If
  
End Sub

Hi Leith,

Thanks for the re worked code however this code is causing a strange problem when I call it.

The Sub i'm calling the ChkJobNo from has code in it to check cells to see if there's anything in them and if not it brings up a message box telling you that there should be and then copies info from one sheet to another.

When I call ChkJobNo and the number isn't found which it's searching for it causes the other Sub code to think there are empty cells when there are not.

Why is this happening? The ChkJobNo is called at the top of the other subs code. I'll actually paste the other sub code below for your reference.

Code:

Sub CreateSN()
'
' CreateSN Macro
'
Dim rval As Integer, ID As Integer, X As String, JobNo As String, SubName As String, Foreman As String, Super As String, Value As Double, Client As String

Call ChkJobNo

ID = Cells(1, 2)
JobNo = Cells(6, 2).Value
ProjectName = Cells(7, 2).Value
SubName = Cells(8, 2).Value
Foreman = Cells(9, 2).Value
Super = Cells(10, 2).Value
Value = Cells(11, 2).Value
Client = Cells(13, 2).Value
Address = Cells(14, 2).Value
Suburb = Cells(15, 2).Value
Town = Cells(16, 2).Value
Postcode = Cells(17, 2).Value



'Checks to make sure if data that is required has been entered.'

JobNo = Cells(6, 2).Value
ProjectName = Cells(7, 2).Value
SubbieName = Cells(8, 2).Value
Foreman = Cells(9, 2).Value
Super = Cells(10, 2).Value
Value = Cells(11, 2).Value
Client = Cells(13, 2).Value
Address = Cells(14, 2).Value
Suburb = Cells(15, 2).Value
Town = Cells(16, 2).Value
Postcode = Cells(17, 2).Value

If JobNo = "" Then
    MsgBox ("You can't make a new form without a Job No dumbass")
End
   End If
   
If ProjectName = "" Then
    MsgBox ("Is alzimers setting in early and you can't remember what the Project is called?")
End
    End If
    
If SubbieName = "" Then
    MsgBox ("What was the sub contractors name again?")
End
    End If
    
If Foreman = "" Then
    MsgBox ("Ok,Ok, I know it's a hard one but what's your name?")
End
    End If
    
If Super = "" Then
    MsgBox ("So there isn't a Super intendent for this job?")
End
    End If
    
If Value = 0 Then
    MsgBox ("Show me the money!...put in a value.")
End
    End If
    
If Client = "" Then
    MsgBox ("So who is the client contact meant to be?")
End
    End If

'If Address = "" Then'
 '   MsgBox ("Where does the client live?")'
'End'
 '   End If'

'If Suburb = "" Then
 '   MsgBox ("Err... I think you forgot the suburb")
'End
 '   End If
    

'If Town = "" Then
'    MsgBox ("What city do they live in?")
'End
 '   End If

'If Postcode = "" Then
 '   MsgBox ("Enter the postcode!")
'End
'    End If
 'Calls Script to check if the job number alrady exists'
'Finds the last row and copies the information from Main'

If ID = 1 Then

    Sheets("Core").Select
    rval = 1
    While Not Cells(rval, 1).Value = ""
        rval = rval + 1
        
    Wend
    Cells(rval, 1) = JobNo
    Cells(rval, 2) = ProjectName
    Cells(rval, 3) = SubName
    Cells(rval, 4) = Foreman
    Cells(rval, 5) = Super
    Cells(rval, 6) = Value
    Cells(rval, 7) = Client
    Cells(rval, 8) = Address
    Cells(rval, 9) = Suburb
    Cells(rval, 10) = Town
    Cells(rval, 11) = Postcode
    
    
'removes borders'
    Rows(rval).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    Sheets("Main").Select
    
  Range("B6").Select
    Application.CutCopyMode = False
    

    
End If
'Checks to make sure if data that is required has been entered.'

JobNo = Cells(6, 2).Value
ProjectName = Cells(7, 2).Value
SubbieName = Cells(8, 2).Value
Foreman = Cells(9, 2).Value
Super = Cells(10, 2).Value
Value = Cells(11, 2).Value
Client = Cells(13, 2).Value
Address = Cells(14, 2).Value
Suburb = Cells(15, 2).Value
Town = Cells(16, 2).Value
Postcode = Cells(17, 2).Value

If JobNo = "" Then
    MsgBox ("You can't make a new form without a Job No dumbass")
End
   End If
   
If ProjectName = "" Then
    MsgBox ("Is alzimers setting in early and you can't remember what the Project is called?")
End
    End If
    
If SubbieName = "" Then
    MsgBox ("What was the sub contractors name again?")
End
    End If
    
If Foreman = "" Then
    MsgBox ("Ok,Ok, I know it's a hard one but what's your name?")
End
    End If
    
If Super = "" Then
    MsgBox ("So there isn't a Super intendent for this job?")
End
    End If
    
If Value = 0 Then
    MsgBox ("Show me the money!...put in a value.")
End
    End If
    
If Client = "" Then
    MsgBox ("So who is the client contact meant to be?")
End
    End If

'If Address = "" Then
 '   MsgBox ("Where does the client live?")
'End
 '   End If

'If Suburb = "" Then
 '   MsgBox ("Err... I think you forgot the suburb")
'End
 '   End If
    

'If Town = "" Then
 '   MsgBox ("What city do they live in?")
'End
 '   End If

'If Postcode = "" Then
 '   MsgBox ("Enter the postcode!")
'End
 '   End If

Sheets("SN1").Select




   
End Sub