Search for partial value

  • 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.

    Any assistance would be appreciated. :)

    Thank you,


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

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

  • 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.

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

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

    If so, maybe try changing this to :

    1. .Cells(lr + 1, "C").Value = DateValue(Format(Me.txtReceived.Value, "mm/dd/yyyy"))
    2. .Cells(lr + 1, "D").Value = DateValue(Format(Me.txtMoved.Value, "mm/dd/yyyy"))
    3. .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.