Find exact match in column not working

[jc352]

I am trying to find the exact match of the input in textbox2 to the information in column B and populate text and comboboxes with the information contained in the cells in that row. If there is no match in column B to the information input in the textbox, then I would like the information to be added to the worksheet in the next available row. The code I have works when there is no information in the column, but when the column is populated it always says it has found a match regardless of the information in it. Can someone please help me out.

Private Sub CommandButton2_Click()
With Sheets("jobs test")
' check for existing job
If Not .Range("B:B").Find(What:=TextBox2.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then .Range("B:B").Find(What:=TextBox2.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate

where = ActiveCell.Offset(0, -1)
TextBox3.Value = ActiveCell.Offset(0, 1)
restdaysset = ActiveCell.Offset(0, 2)
shiftstart1 = ActiveCell.Offset(0, 3)
shiftend1 = ActiveCell.Offset(0, 4)
shiftstart2 = ActiveCell.Offset(0, 5)
shiftend2 = ActiveCell.Offset(0, 6)
shiftstart3 = ActiveCell.Offset(0, 7)
shiftend3 = ActiveCell.Offset(0, 8)
shiftstart4 = ActiveCell.Offset(0, 9)
shiftend4 = ActiveCell.Offset(0, 10)
shiftstart5 = ActiveCell.Offset(0, 11)
shiftend5 = ActiveCell.Offset(0, 12)
shiftstart6 = ActiveCell.Offset(0, 13)
shiftend6 = ActiveCell.Offset(0, 14)
shiftstart7 = ActiveCell.Offset(0, 15)
shiftend7 = ActiveCell.Offset(0, 16)
MsgBox ("Job Exists")
GoTo exists
' Add new job
Dim LastRow As Long
LastRow = Sheets("jobs test").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Set MyRange = Sheets("jobs test").Range("a1:a20")
MyRange.Cells(LastRow).Offset(0, 0) = where
MyRange.Cells(LastRow).Offset(0, 1) = TextBox2.Value
MyRange.Cells(LastRow).Offset(0, 2) = TextBox3.Value
MyRange.Cells(LastRow).Offset(0, 3) = restdaysset
MyRange.Cells(LastRow).Offset(0, 4) = shiftstart1
MyRange.Cells(LastRow).Offset(0, 5) = shiftend1
MyRange.Cells(LastRow).Offset(0, 6) = shiftstart2
MyRange.Cells(LastRow).Offset(0, 7) = shiftend2
MyRange.Cells(LastRow).Offset(0, 8) = shiftstart3
MyRange.Cells(LastRow).Offset(0, 9) = shiftend3
MyRange.Cells(LastRow).Offset(0, 10) = shiftstart4
MyRange.Cells(LastRow).Offset(0, 11) = shiftend4
MyRange.Cells(LastRow).Offset(0, 12) = shiftstart5
MyRange.Cells(LastRow).Offset(0, 13) = shiftend5
MyRange.Cells(LastRow).Offset(0, 14) = shiftstart6
MyRange.Cells(LastRow).Offset(0, 15) = shiftend6
MyRange.Cells(LastRow).Offset(0, 16) = shiftstart7
MyRange.Cells(LastRow).Offset(0, 17) = shiftend7
 
 
 
End With
exists:
End Sub

 

[tonyyy]

jc352,

You might consider using an IF/Else/End If construct...

Private Sub CommandButton2_Click()
With Sheets("jobs test")
    ' check for existing job
    If Not .Range("B:B").Find(What:=TextBox2.Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
        .Range("B:B").Find(What:=TextBox2.Value, LookIn:=xlValues, LookAt:=xlWhole).Activate
        where = ActiveCell.Offset(0, -1)
        TextBox3.Value = ActiveCell.Offset(0, 1)
        restdaysset = ActiveCell.Offset(0, 2)
        shiftstart1 = ActiveCell.Offset(0, 3)
        shiftend1 = ActiveCell.Offset(0, 4)
        shiftstart2 = ActiveCell.Offset(0, 5)
        shiftend2 = ActiveCell.Offset(0, 6)
        shiftstart3 = ActiveCell.Offset(0, 7)
        shiftend3 = ActiveCell.Offset(0, 8)
        shiftstart4 = ActiveCell.Offset(0, 9)
        shiftend4 = ActiveCell.Offset(0, 10)
        shiftstart5 = ActiveCell.Offset(0, 11)
        shiftend5 = ActiveCell.Offset(0, 12)
        shiftstart6 = ActiveCell.Offset(0, 13)
        shiftend6 = ActiveCell.Offset(0, 14)
        shiftstart7 = ActiveCell.Offset(0, 15)
        shiftend7 = ActiveCell.Offset(0, 16)
        MsgBox ("Job Exists")
        GoTo exists
    Else
        ' Add new job
        Dim LastRow As Long
        LastRow = Sheets("jobs test").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
        Set MyRange = Sheets("jobs test").Range("a1:a20")
        MyRange.Cells(LastRow).Offset(0, 0) = where
        MyRange.Cells(LastRow).Offset(0, 1) = TextBox2.Value
        MyRange.Cells(LastRow).Offset(0, 2) = TextBox3.Value
        MyRange.Cells(LastRow).Offset(0, 3) = restdaysset
        MyRange.Cells(LastRow).Offset(0, 4) = shiftstart1
        MyRange.Cells(LastRow).Offset(0, 5) = shiftend1
        MyRange.Cells(LastRow).Offset(0, 6) = shiftstart2
        MyRange.Cells(LastRow).Offset(0, 7) = shiftend2
        MyRange.Cells(LastRow).Offset(0, 8) = shiftstart3
        MyRange.Cells(LastRow).Offset(0, 9) = shiftend3
        MyRange.Cells(LastRow).Offset(0, 10) = shiftstart4
        MyRange.Cells(LastRow).Offset(0, 11) = shiftend4
        MyRange.Cells(LastRow).Offset(0, 12) = shiftstart5
        MyRange.Cells(LastRow).Offset(0, 13) = shiftend5
        MyRange.Cells(LastRow).Offset(0, 14) = shiftstart6
        MyRange.Cells(LastRow).Offset(0, 15) = shiftend6
        MyRange.Cells(LastRow).Offset(0, 16) = shiftstart7
        MyRange.Cells(LastRow).Offset(0, 17) = shiftend7
    End If
End With
exists:
End Sub

Of course, this code is untested.

Cheers,

tonyyy

 

[jc352]

Thank you so much, it works perfectly.

 

[tonyyy]

You're very welcome...