To get List of Positions of different strings in a text file in a Textbox - Part 1 After getting the positions i would ...... Part 2

[NimishK]

Hello

Part 1
As the following code results for Single searched String and getting its Position in Line Number of a text file.
I was wondering if a List of Array of Strings could be searched and getting Position of Each String with Line number one below another in a text box as per below Result in quote marks

MySearch = GetSearchResults("C:\NimishK\strPos.txt", "RefID:")
I wanted to use the above with below Syntax

MySearch = GetSearchResults("C:\NimishK\strPos.txt", arrayStringsRev(j))

Miserably failed in deriving the result
MrExcel Thread: 1148864

Module:
Public Type SearchResults
    LineNumber As Long
    CharPosition As Long
    StrLen As Long
End Type

Option Explicit

Private Sub UserForm_Initialize()
   Call Example
End Sub

Sub Example()
Dim txtFileName As String, strData As String
Dim MySearch() As SearchResults, i As Long

Dim arrayStringsRev(4) As String
arrayStringsRev(1) = "RefID:"
arrayStringsRev(2) = "Name:"
arrayStringsRev(3) = "Area"
arrayStringsRev(4) = "Amount"


   For j = LBound(arrayStringsRev) To UBound(arrayStringsRev)
     MySearch = GetSearchResults("C:\NimishK\strPos.txt", arrayStringsRev(j))
  
   ''''' MySearch = GetSearchResults("C:\NimishK\strPos.txt", "RefID:")
    For i = 0 To UBound(MySearch)
        With MySearch(i)
        strData = " is in line " & .LineNumber & " Position " & .CharPosition & " Length " & .StrLen

        End With
    Next
    j = j + 1
    Next

    txtBox4.Text = txtBox4.Text & strData
End Sub

Function GetSearchResults( _
  FileFullName As String, _
  FindThis As String, _
  Optional CompareMethod As VbCompareMethod = vbBinaryCompare) _
  As SearchResults()
 
    Dim fso As New FileSystemObject, s As String, pos As Long, l As Long, sr As SearchResults, ret() As SearchResults, i As Long
   
    With fso.OpenTextFile(FileFullName)
        Do Until .AtEndOfStream
            l = l + 1
            s = .ReadLine
            pos = 1
            Do
                pos = InStr(pos, s, FindThis, CompareMethod)
                If pos > 0 Then
                    sr.CharPosition = pos
                    sr.LineNumber = l
                    sr.StrLen = Len(FindThis)
                    ReDim Preserve ret(i)
                    ret(i) = sr
                    i = i + 1
                    pos = pos + 1
                End If
            Loop Until pos = 0
        Loop
    End With
    GetSearchResults = ret
End Function

Result
RefID: is in line 1 Position 25 Length 6
Name: is in line 2 Position 5 Length 5
Area: is in line 2 Position 15 Length 5
Amount is in line 3 Position 15 Length 6

Part 2:
After getting the positions i would like to Carrage Return / vbcrlf/vbNewline at the list of positions

Will always appreciate your help

Thanks
NimishK

 

[NimishK]

Hello

Can someone really look into this and help me code a function which will at least help me to resolve Part 1 of the query
or would really appreciate if any link could be forwarded on a similar type of question.

FYI i've never been able to create any function very smoothly.

Thanks
NimishK

 

[Alex Blakenburg]

I am not 100% on where you have gotten too.

Try these 2 changes:-
1) Lbound(arrayStringsRev) was starting at 0 which meant it went looking for ""

Dim arrayStringsRev(1 To 4) As String

2) Concatenating the values and adding a Line Feed at the end.

strData = strData & arrayStringsRev(j) & ": is in line " & .LineNumber & " Position " & .CharPosition & " Length " & .StrLen & vbLf

 

[NimishK]

Alex
Thanks

Changed as per your suggestion
but i get error "Subscript out of range"with the below tries

at this line
For i = 0 To UBound(MySearch) ie UBound(MySearch) = <Subscript out of range>
Even with
For i = 1 To UBound(MySearch)

It seems some core coding required in Function GetSearchResults(......

Creating Functions with arrays , array of arrays etc. and other complicated Functions i am unable to understand and incorporate the same

NimishK

 

[Alex Blakenburg]

Can you confirm that you did this step ?

You will need a reference to Microsoft Scripting Runtime.

Ref: MrExcel Thread: 1148864

 

[NimishK]

Alex

Can you confirm that you did this step ?

Without MS-Scripting Runtime. One will not be able to read the File nor FSO would execute

Yes, Very much MS-Scripting Runtime is Enabled in References.

NimishK

 

[Alex Blakenburg]

Can you comment out this line and see if it makes a difference.
It is working for me and it is one of the changes I made to get it to work for me.

    j = j + 1

 

[Alex Blakenburg]

The j = j + 1 removal and the
changing the 1 to 0 in this:
For i = 0 To UBound(MySearch)

Are the only real changes I made for it to work at my end.

and then this, to give you 1 item with each found result on a new line.

strData = strData & arrayStringsRev(j) & ": is in line " & .LineNumber & " Position " & .CharPosition & " Length " & .StrLen & vbLf

 

[NimishK]

Can you comment out this line and see if it makes a difference.
It is working for me and it is one of the changes I made to get it to work for me.

VBA Code:
j = j + 1

Yes Already commented out. ie 'j = j + 1

The j = j + 1 removal and the
changing the 1 to 0 in this:
For i = 0 To UBound(MySearch)

Removed j = j + 1

For i = 0 To UBound(MySearch)
do i need to change above to below
For i = 1 To UBound(MySearch)

Still Subscript out of Range Error

NimishK

 

[NimishK]

Got rid of Subscript out of range Error

All the arrayStringsRev(j) are displayed repeatedly. unfortunately Line nos, char position string lenght are displaying once but repeated

NimishK