Find exact match in column not working

jc352

Board Regular
Joined
Aug 13, 2008
Messages
134
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
jc352,

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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top