CODEWITHSUNDEEP
excelvba
Learning and Improving
Learning and Improving

Reputation: 81

How to Match just part of a cell's value instead of an exact match in VBA for Excel?

I have already written a script that works fine for exact matching, but I would like to rewrite it to allow for the user to type only part of one of the criteria. The userform should still recognize what row it came from on the other sheet and populate the cells with it's corresponding information. The row has to contain both txtsearch and txtname for it to be correct row found.

Sample values for txtsearch: Instuctor, Mentor, etc.

Sample values for txtname: Borkowski, Villarreal, etc.

This is what I have now:

Private Sub CommandButton1_Click()
Dim ws As Worksheet, cel As Range
    Set ws = Sheets("The Goods")
    For Each cel In ws.Cells(2, 2).Resize(ws.Cells(Rows.Count, 2).End(xlUp).Row).Cells
        If cel.Value = Me.txtname.Value And cel.Offset(, 2).Value = Me.txtsearch.Value Then
           currentrow = cel.Row
            Me.txt1.Value = cel.Offset(, 3).Value
            Me.txt2.Value = cel.Offset(, 1).Value
            Me.txt3.Value = cel.Offset(, 4).Value
            Me.txt4.Value = cel.Offset(, 5).Value
            Me.txt5.Value = cel.Offset(, 6).Value
            Me.txt6.Value = cel.Offset(, 7).Value
            Me.txt7.Value = cel.Offset(, 8).Value
            Me.txt8.Value = cel.Offset(, 9).Value
            Me.txt9.Value = cel.Offset(, 10).Value
            Me.txt10.Value = cel.Offset(, 11).Value
            Me.txt11.Value = cel.Offset(, 12).Value
        End If
    Next cel
End Sub

Upvotes: 0

Views: 130

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

If you mean "only part" then the instr function is what you need. See below...

If InStr(1, cel.Value, Me.txtname.Value, vbTextCompare) > 0 And  _ 
   InStr(1, cel.Offset(, 2).Value, Me.txtsearch.Value) > 0 Then

If you meant only to the left, this would work:

If Left(cel.Value, Len(Me.txtname.Value)) = Me.txtname.Value And _ 
   Left(cel.Offset(, 2).Value, Len(Me.txtname.Value)) = Me.txtname.Value Then

Upvotes: 1

Related Questions