look up in a collection

Question

I have this code (from here: https://stackoverflow.com/a/61501053/11458676) to load a csv file and pick some elements.

    Option Explicit

Function txtfileinCol(filename As String) As Collection
' loads the content of a textfile line by line into a collection
    Dim fileContent As Collection
    Set fileContent = New Collection

    Dim fileNo As Long
    Dim txtLine As String

    fileNo = FreeFile
    Open filename For Input As #fileNo
    Do Until EOF(fileNo)
        Line Input #fileNo, txtLine
        fileContent.Add txtLine
    Loop

    Close #fileNo

    Set txtfileinCol = fileContent

End Function
Function getColRow(fileLines As Collection, rowNo As Long, colNo As Long, Optional delimiter As String) As String

    Dim vdat As Variant

    On Error GoTo EH:

    If Len(delimiter) = 0 Then
        delimiter = ";"
    End If

    vdat = fileLines.Item(rowNo)    'here you get the line
    vdat = Split(vdat, delimiter)   'now you split the line with the delimiter

    getColRow = vdat(colNo - 1)     'now you retrieve the content of the column
    Exit Function
EH:
    getColRow = ""

End Function

Sub Testit()

    Dim filename As String
    Dim col As Collection

    filename = "C:\Temp\FILE.csv"
    Set col = txtfileinCol(filename)   

    Debug.Print getColRow(col, 10, 2, ";") 

End Sub

and want to look up for a specific element. Is there any stright way to search the for the row number of a given string inside the content?

Thanks.

Which 'this code'? How did you "loaded a csv file in memory"? — FaneDuru, May 04 '20 at 12:11

score 0 · Answer 1 · answered May 04 '20 at 13:27

Try this piece of code, please:

Private Function rowFromString(col As Collection, strSearch As String) As Long
   Dim i As Long
   For i = 1 To col.count
        If InStr(col(i), strSearch) > 0 Then
            rowFromString = i: Exit Function
        End If
   Next i
End Function

You can call it as:

Debug.Print rowFromString(col, "your search string")

It will return the collection element number (in your case, the row number) where the string has been found...

look up in a collection

1 Answers1