VBA/UserForm Find Data - Multiple Criteria

[zodiaceuk]

Hi Guys,

I’ve hit a stumbling block with this, hoping someone can help please?

I’m looking to use some VBA code in my userform to find data from multiple criteria.

Table
EmpID Date Reason
123. 24/10/19 Off
345. 10/10/19. AL
123. 25/10/19. AL

I have another userform to enter the data into the table.

My problem is, supposing I need to edit/remove the entry,
Is it possible for another userform to be able to find the row based on all 3 criteria, so that this can be edited/removed please?

I’m using this code on elsewhere, this works great but not sure how to extend this to 3 criteria.

Dim Ran1 as Range
Dim strsrch as String
Dim TBrow as long

Strsrch = me.EmpID.Values
Set Ran1 = Sheets (“Active”). Range(“A:A”).Find(what:strsrch, Lookat:=xlWhole)
If Ran1 is nothing then
Msgbox ‘message details here
Exit sub
End if
TBrow = Ran1.row

Thanks guys

 

[DanteAmor]

I hope the following example helps you

Change data in red for your information.

Private Sub CommandButton1_Click()
  Dim f As Range, strsrch As String, TBrow As Long
  Dim sh As Worksheet, r As Range, cell As String
  Set sh = Sheets("Active")
  Set r = sh.Range("A:A")
  strsrch = Me.EmpID.Value
[COLOR=#008000]  'look for first criteria[/COLOR]
  Set f = r.Find(strsrch, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
[COLOR=#008000]      'look for second and third criteria[/COLOR]
      If sh.Cells(f.Row, "[COLOR=#ff0000]B[/COLOR]").Value = CDate([COLOR=#ff0000]TextBox2[/COLOR].Value) And _
         sh.Cells(f.Row, "[COLOR=#ff0000]C[/COLOR]").Value = [COLOR=#ff0000]TextBox3[/COLOR].Value Then
        TBrow = f.Row
        Exit Do
      End If
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
  If TBrow = 0 Then
    MsgBox "Does not exists"
  Else
    MsgBox "message details here, row: " & TBrow
  End If
End Sub

 

[zodiaceuk]

Excellent, thanks so much DanteAmor ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.