Reading a text file and scan for values

[Danny54]

Good Morning

how would I go about reading a text file and scan for values? If found return the header line.
the text file has a header on line line (Test1)
the second line has all the IP values
between each group is a blank line

the text file looks like this? (test.txt)
Text file on C drive (Windows)

Test1
192.168.0.1,192.168.1.1,200.0.0.0

Test2
10.10.10.1,10.10.10.2


Sheet before containing the values to scan(lookup) in the text file above

lookup value

192.168.1.1

192.1.1.5

Sheet after lookup showing "Header Line where Found"

lookup value	Header Line where Found
192.168.1.1	Test1
192.1.1.5

Thanks

 

[DanteAmor]

Adjust the folder and file name in the code.
Try this:

Sub scan_for_values_txt()
  Dim textline As String, myPath As String, myFile As String, tit As String
  Dim i As Long, n As Long, m As Long
  Dim a As Variant, b As Variant
  
  myPath = "C:\trabajo\"    'Set to your folder name
  myFile = "test.txt"       'Set to your file name
  
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  
  Open myPath & myFile For Input As #1
  While Not EOF(1)
    n = n + 1
    m = n Mod 3
    Line Input #1, textline
    
    If m = 1 Then tit = textline
    If m = 2 Then
      textline = "," & Replace(textline, " ", "") & ","
      For i = 1 To UBound(a, 1)
        If InStr(1, textline, "," & Trim(a(i, 1)) & ",") > 0 Then
          b(i, 1) = tit
        End If
      Next
    End If
  Wend
  Close #1
  
  Range("B2").Resize(UBound(b, 1)).Value = b
End Sub

 

[Danny54]

Thank you DanteAmor
the vba code return the last value found. What modifications should be made to return multiple finds?


Text input C:\trabajo\test.txt
Test1
192.168.1.1,192.168.1.1,200.0.0.0

Test2
10.10.10.1,10.10.10.2

Test3
10.0.0.0,192.168.1.1


Sheet before containing the values to scan(lookup) in the text file above

lookup value

192.168.1.1

192.1.1.5

Sheet after lookup showing "Header Line where Found"

lookup value	Header Line where Found
192.168.1.1	Test1
192.168.1.1	Test3
192.1.1.5

Thanks

 

[DanteAmor]

What modifications should be made to return multiple finds?
...
Sheet after lookup showing "Header Line where Found"

lookup value	Header Line where Found
192.168.1.1	Test1
192.168.1.1	Test3
192.1.1.5

That was not in your PO, it is recommended that you explain all the possible cases from the beginning, so that the answer delivered is as complete as possible, otherwise, my answer will not help you, but it is not that my answer does not work, but it's because you didn't explain in detail what you need.

Try this:

Sub scan_for_values_txt()
  Dim textline As String, myPath As String, myFile As String, tit As String
  Dim i As Long, j As Long, n As Long, m As Long
  Dim a As Variant, b As Variant
  
  myPath = "C:\trabajo\"    'Set to your folder name
  myFile = "test.txt"       'Set to your file name
  
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1) * 100, 1 To 2)
  
  Open myPath & myFile For Input As #1
  While Not EOF(1)
    n = n + 1
    m = n Mod 3
    Line Input #1, textline
    
    If m = 1 Then tit = textline
    If m = 2 Then
      textline = "," & Replace(textline, " ", "") & ","
      For i = 1 To UBound(a, 1)
        If InStr(1, textline, "," & Trim(a(i, 1)) & ",") > 0 Then
          j = j + 1
          b(j, 1) = a(i, 1)
          b(j, 2) = tit
        End If
      Next
    End If
  Wend
  Close #1
  
  Range("A2").Resize(j, 2).Value = b
End Sub

 

[Danny54]

Thanks DaunteAmor

Sorry for the lack of information. The narrative should have said was to read values from the workbook sheet1 cell by cell column a.
For each value in sheet1 do a lookup in the txt file

192.168.1.1

192.1.1.5

If found list the header and value where its found and as many times as its found

lookup value	Header Line where Found
192.168.1.1	Test1
192.168.1.1	Test3

If not found just list the lookup value and return to the next value in sheet

lookup value	Header Line where Found
192.168.1.1

So for the three input test value the output sheet would show

lookup value	Header Line where Found
192.168.1.1	Test1
192.168.1.1	Test3
192.1.1.5

The last code above drops the values where no header is found

Again,
thanks

 

[DanteAmor]

Try:

Sub scan_for_values_txt_2()
  Dim textline As String, myPath As String, myFile As String, tit As String
  Dim i As Long, j As Long, n As Long, m As Long
  Dim a As Variant, b As Variant
  Dim fnd As Boolean
  
  myPath = "C:\trabajo\"    'Set to your folder name
  myFile = "test.txt"       'Set to your file name
  
  a = Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1) * 100, 1 To 2)
  
  Open myPath & myFile For Input As #1
  For i = 1 To UBound(a, 1)
    n = 0
    fnd = False
    Seek #1, 1
    While Not EOF(1)
      n = n + 1
      m = n Mod 3
      Line Input #1, textline
      
      If m = 1 Then tit = textline
      If m = 2 Then
        textline = "," & Replace(textline, " ", "") & ","
        If InStr(1, textline, "," & Trim(a(i, 1)) & ",") > 0 Then
          j = j + 1
          b(j, 1) = a(i, 1)
          b(j, 2) = tit
          fnd = True
        End If
      End If
    Wend
    If fnd = False Then
      j = j + 1
      b(j, 1) = a(i, 1)
    End If
  Next
  Close #1
  
  Range("A2").Resize(j, 2).Value = b
End Sub

 

[Danny54]

Perfect.

Thanks so much.

I'll be more specific in the future.

Have a wonderful week

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.