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

Well-known Member
Joined
Sep 4, 2015
Messages
660
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

VBA Code:
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

 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,123
Office Version
  1. 365
Platform
  1. Windows
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 ""
VBA Code:
Dim arrayStringsRev(1 To 4) As String

2) Concatenating the values and adding a Line Feed at the end.
VBA Code:
strData = strData & arrayStringsRev(j) & ": is in line " & .LineNumber & " Position " & .CharPosition & " Length " & .StrLen & vbLf
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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
 
Last edited:

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,123
Office Version
  1. 365
Platform
  1. Windows
Can you confirm that you did this step ?

You will need a reference to Microsoft Scripting Runtime.
Ref: MrExcel Thread: 1148864

1631597631536.png

1631597392384.png
 
Last edited:

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,123
Office Version
  1. 365
Platform
  1. Windows
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
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,123
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
strData = strData & arrayStringsRev(j) & ": is in line " & .LineNumber & " Position " & .CharPosition & " Length " & .StrLen & vbLf
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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
 
Last edited:

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
660
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
 

Forum statistics

Threads
1,176,238
Messages
5,902,089
Members
434,939
Latest member
MrDrock

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top