Search for partial value

VBA_newbie_2021 · Jun 8th 2021

Hello. I'm new to using macros and I'm trying to create one for keeping track of packages within my company's building. I've been able to Frankenstein a usable macro together, but I'm having trouble with the Search.

When we scan the FedEx barcode, it shows the entire code. However, if someone only knows the 12 digit tracking number that is printed on the label, I want to be able to find it within the larger string of numbers.

For instance, if I scan a tracking number, here is what comes out 1002882030350004522700785515984311. However, I want the macro to be able to search for both the long number as well as 785515984311, which is what is printed on the FedEx tracking label.

Also, I don't understand why the message box pops up three times.

Here's my current code below. This searches for the entire code. I've also attached the document.

Code

Private Sub cmdSearch_Click()
Dim x As Long
Dim y As Long
x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
For y = 6 To x


If Sheets("Worksheet").Cells(y, 1).Value = txtSearch.Text Then
txtTracking = Sheets("Worksheet").Cells(y, 1).Value
txtLocation = Sheets("Worksheet").Cells(y, 2).Value
txtReceived = Sheets("Worksheet").Cells(y, 3).Value
txtMoved = Sheets("Worksheet").Cells(y, 4).Value
txtCompleted = Sheets("Worksheet").Cells(y, 5).Value


Else
MsgBox "Tracking Not Found."
txtTracking.SetFocus


End If


Next y


End Sub

Display More

Any assistance would be appreciated.

Thank you,

Becky

gijsmo · Jun 9th 2021

For a partial string match anywhere in the string maybe try the following:

Code

Private Sub cmdSearch_Click()
    Dim x As Long
    Dim y As Long
    Dim Found As Boolean
    
    x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
    
    Found = False
    If Trim(txtSearch.Text) <> "" Then
      For y = 6 To x
        If InStr(1, CStr(Sheets("Worksheet").Cells(y, 1).Value), CStr(txtSearch.Text), vbTextCompare) Then
            txtTracking = Sheets("Worksheet").Cells(y, 1).Value
            txtLocation = Sheets("Worksheet").Cells(y, 2).Value
            txtReceived = Sheets("Worksheet").Cells(y, 3).Value
            txtMoved = Sheets("Worksheet").Cells(y, 4).Value
            txtCompleted = Sheets("Worksheet").Cells(y, 5).Value
            Found = True
            Exit For
        End If
      Next y
    End If
    
    If Not Found Then
      MsgBox "Tracking Not Found."
      txtTracking.SetFocus
    End If
End Sub

Display More

This will of course match anywhere in the string, if you specifically want to only match the end of the string you could try:

Code

Private Sub cmdSearch_Click()
    Dim x As Long
    Dim y As Long
    Dim Found As Boolean
    
    x = Sheets("Worksheet").Range("A" & Rows.Count).End(xlUp).Row
    
    Found = False
    If Trim(txtSearch.Text) <> "" Then
      For y = 6 To x
        If CStr(Sheets("Worksheet").Cells(y, 1).Value) = CStr(txtSearch.Text) Or _
           Right(CStr(Sheets("Worksheet").Cells(y, 1).Value), Len(CStr(txtSearch.Text))) = CStr(txtSearch.Text) Then
            txtTracking = Sheets("Worksheet").Cells(y, 1).Value
            txtLocation = Sheets("Worksheet").Cells(y, 2).Value
            txtReceived = Sheets("Worksheet").Cells(y, 3).Value
            txtMoved = Sheets("Worksheet").Cells(y, 4).Value
            txtCompleted = Sheets("Worksheet").Cells(y, 5).Value
            Found = True
            Exit For
        End If
      Next y
    End If
    
    If Not Found Then
      MsgBox "Tracking Not Found."
      txtTracking.SetFocus
    End If
End Sub

Display More

VBA_newbie_2021 · Jun 9th 2021

Thank you so much! This worked beautifully!!!

gijsmo · Jun 9th 2021

You are welcome.

VBA_newbie_2021 · Jun 9th 2021

One more question.

Why when I update the record does the date formatting change? When I save the initial record, the date format is mm/dd/yyyy, but when I update it, it puts the date format exactly how you enter it in the date textbox.

For instance, when I save the initial record as 6/1/21, it puts it in the spreadsheet as 06/01/2021, but when I update the record and put 6/1/21, it puts it in the spreadsheet as 6/1/21.

It's not a big deal, just a minor annoyance because the date format isn't uniform.

gijsmo · Jun 10th 2021

I assume you are referring to the following bit of code:

Code

        .Cells(lr + 1, "C").Value = Me.txtReceived.Value
        .Cells(lr + 1, "D").Value = Me.txtMoved.Value
        .Cells(lr + 1, "E").Value = Me.txtCompleted.Value

If so, maybe try changing this to :

Code

        .Cells(lr + 1, "C").Value = DateValue(Format(Me.txtReceived.Value, "mm/dd/yyyy"))
        .Cells(lr + 1, "D").Value = DateValue(Format(Me.txtMoved.Value, "mm/dd/yyyy"))
        .Cells(lr + 1, "E").Value = DateValue(Format(Me.txtCompleted.Value, "mm/dd/yyyy"))

Also, make sure the cells you are writing to are formatted with the "mm/dd/yyyy" format.

Search for partial value

Participate now!

Share