1

I have a range of cells (A1:C13) as shown in the picture.

I wrote code to search for cells within this range that contain the string "Node" and then grab the contents from the cell directly below the matched cell. The code reports the results in the format Node XX = XXX in column E of the same sheet.

My aim is to directly export the results of my loop into a text file as shown in the picture. I searched the forums for a solution.

Picture of my search range, my current results and my expected results: enter image description here

Sub Find_Nodes_text()
    Dim CompId As Range
    Dim i As Byte
    Dim FirstMatch As Variant
    Dim TXT As String

    Range("E:E").ClearContents
    i = 1

    Set CompId = Range("A1:C13").Find(what:="Node", LookIn:=xlValues, lookat:=xlPart)
    If Not CompId Is Nothing Then
        TXT = CompId.Value & " = " & CompId.Offset(1, 0).Value
        Range("E" & i).Value = TXT
        FirstMatch = CompId.Address
        Do
            Set CompId = Range("A1:C13").FindNext(CompId)
            If CompId.Address = FirstMatch Then Exit Do
            i = i + 1
            TXT = CompId.Value & " = " & CompId.Offset(1, 0).Value
            Range("E" & i).Value = TXT
        Loop
    Else
    MsgBox "No Nodes Found!"
    End If
End Sub
Community
  • 1
  • 1
WarEagleCivil
  • 13
  • 2
  • 2
    https://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba – Tim Williams Feb 09 '20 at 08:03
  • @TimWilliams that link must be the best FSO answer I have ever seen on SO, excellent response! – ProfoundlyOblivious Feb 09 '20 at 19:24
  • Thank you, Mr.Williams for pointing me in the right direction. I found the code posted by Mr. Marcus Mangelsdorf (https://stackoverflow.com/a/49674605/12854709) useful to me. The comments he put inside his code made it easy to use. – WarEagleCivil Feb 11 '20 at 13:02

0 Answers0