Reading a text file in vba and modifying a certain

Question

I started coding in vba today for the first time. My code should ask for a user input first and then read from a text file. If the user input matches the 100 number in the text file it should then populate a certain cell with whatever is next to the 100 number, until the - in the worksheet. If it is GHF897-HTU71L then it should populate the cell until GHF897.

Here is the text file

HGJDJS UFHFHFHB HHGG 0001    100000896765    GHF897-HTU71L-7811-YTHIN            U1P903678    GHF897_0000000016       000001    |||||||    W69      000001    |||||||---    X72    BAL TAG    000002    |||||||    X75    CONFIG    000001    |||||||

 GFHRTE 0001    100657853125   FGD867-YTURGL-TT55-YTHU/NH7            U1P903679    HFJDJH1_0000000015   

---

Sub readFile()
    Dim myValue As Variant

    myValue = InputBox("Please Enter/Scan the order number", "Order Number")

    Dim arr() As String
    Dim i As Integer

    Const strFileName As String = "C:\text2.txt"

    FileNum = FreeFile

    Open strFileName For Input As #FileNum

    Line Input #FileNum, StrBuffer

    i = 0

    Do Until EOF(1)
        arr = Split(StrBuffer, vbTab)

        If arr(i) = myValue Then Range("D4").Value = arr(i + 1)

        i = i + 1

        'End If
    Loop

    Close #1
End Sub

It should populate the cell "d4" with the value of arr(i+1)

Answer 1

If your "100 number" is slways within a string such as 'GHF897-' (letter, letter, letter, number, number, number, dash) you can use RegEx to look for it. The function below searches input string for a match and returns it. You should be able to manipulate the output string to suit your needs. There is a multitude of RegEx tutorial which can be easily found online or you can search StackOverflow for more examples and ideas.

Function MatchNumber(str As String) As String

    Dim intNo As Integer
    Dim RegEx As Object
    Dim obj As Object

    Set RegEx = CreateObject("vbscript.regexp")

    RegEx.Pattern = "[A-Z][A-Z][A-Z][0-9][0-9][0-9]-"

    Set obj = RegEx.Execute(str)

    MatchNumber = obj(0)

End Function