Loop through text file

[Bercilak]

I have a macro that reads through a text file pasted into a spreadsheet and extracts certain fields into another tab. The method I had been using was to actually split the text into two columns and set up data filters and filtered on certain fields and then copied that data to the other tab. The issue is that the data points are not always consistent, so one data set might have 5 items, while another might have 6, so when they are copied to the other tab, they do not line up in all runs. So, I started working on a loop to pull through the information. I am having problems getting through without getting into an infinite loop.

Here is one subset of data that I run through (numbering provided for reference). I key off of line 2, and then pull data for lines 1-12. Lines 1-12 are consistent, so this part I can do without a problem. The issue is with getting lines 14-21, 23, 25 and 34, which will not always be numbered like this, for instance, the field "Co-Manager" could have 0-12 items.


1 U/L Tax Sch Bldg & Ref Bds Ser 2003
2 Tax Treatment: Tax Exempt
3 Original Issue Amount $156,665,000.00
4 Dated Date: 06/15/2003
5 Sale Date: 06/26/2003
6 Delivery Date: 07/15/2003
7 Sale Type: Negotiated
8 Record Date: MSRB
9 Bond Form: BE
10 Denomination $5,000
11 Interest pays Semi-Annually: 08/15, 02/15
12 1st Coupon Date: 02/15/2004
13
14 Paying Agent: The Bank of New York Mellon Trust Company, N.A., Dallas, TX
15 Bond Counsel: Vinson & Elkins
16 Co-Bond Counsel: West & Gooden, P.C.
17 Financial Advisor: Banc of America Securities LLC
18 Co-Financial Advisor: Siebert Brandford Shank & Co.
19 Lead Manager: William R. Hough & Co.
20 Co-Manager: Estrada Hinojosa & Company, Inc.
21 Co-Manager: First Southwest Company
22
23 Insurance: PSF
24
25 Use of Proceeds: Refunding, School Building.
26
27
28 Orig Reoffering
29 Maturity Amount Coupon Price/Yield
30
31 02/15/2014 50,000.00 3.4000% 3.470%
32 ----------------------------------------------------$50,000.00
33
34 Call Option: Bonds maturing on 02/15/2014 callable in whole or in part on any date beginning 02/15/2013 @ par.

The portion of the script below is what I am working on. X is the line number of text, LZ is the total number of lines in the text and Z is the row for the new tab. Does anyone have any recommendations on how to simplify this process? Thank you in advance!!!


Do While x < lz
If Range("a" & x).Value = "Tax Treatment" Then
z = z + 1
Worksheets("Debt Worksheet").Range("A" & z).Value = .Cells(x - 1, "A").Value 'Series
Worksheets("Debt Worksheet").Range("B" & z).Value = .Cells(x, "B").Value 'Tax Treatment
Worksheets("Debt Worksheet").Range("C" & z).Value = .Cells(x + 1, "A").Value 'Par Amount
Worksheets("Debt Worksheet").Range("D" & z).Value = .Cells(x + 2, "B").Value 'Dated
Worksheets("Debt Worksheet").Range("E" & z).Value = .Cells(x + 3, "B").Value 'Sale
Worksheets("Debt Worksheet").Range("F" & z).Value = .Cells(x + 4, "B").Value 'Delivery
Worksheets("Debt Worksheet").Range("G" & z).Value = .Cells(x + 5, "B").Value 'Sale Type
Worksheets("Debt Worksheet").Range("H" & z).Value = .Cells(x + 6, "B").Value 'Record Date
Worksheets("Debt Worksheet").Range("I" & z).Value = .Cells(x + 7, "B").Value 'Bond Form
Worksheets("Debt Worksheet").Range("J" & z).Value = .Cells(x + 8, "B").Value 'Denomination
Worksheets("Debt Worksheet").Range("K" & z).Value = .Cells(x + 9, "B").Value 'Interest Pays
Worksheets("Debt Worksheet").Range("L" & z).Value = .Cells(x + 10, "B").Value 'First Coupon Date
x = x + 11


Do While Range("a" & x).Value <> "Tax Treatment"
If x > lz Then
Stop
End If

If Range("a" & x).Value = "Paying Agent" Then
Worksheets("Debt Worksheet").Range("M" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Bond Counsel" Then
Worksheets("Debt Worksheet").Range("N" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Co-Bond Counsel" Then
Worksheets("Debt Worksheet").Range("O" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Financial Advisor" Then
Worksheets("Debt Worksheet").Range("P" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Co-Financial Advisor" Then
Worksheets("Debt Worksheet").Range("Q" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Lead Manager" Then
Worksheets("Debt Worksheet").Range("R" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Co-Manager" Then
Worksheets("Debt Worksheet").Range("S" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Insurance" Then
Worksheets("Debt Worksheet").Range("T" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Lead Manager" Then
Worksheets("Debt Worksheet").Range("U" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Use of Proceeds" Then
Worksheets("Debt Worksheet").Range("V" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If
If Range("a" & x).Value = "Call Option" Then
Worksheets("Debt Worksheet").Range("W" & z).Value = .Cells(x, "B").Value 'Tax Treatment
End If

x = x + 1
Loop
x = x - 2
End If
x = x + 1

Loop

 

[wigi]

Hello

I would go for a solution along these lines:

Sub wigi()

    Const c0 As String = "Co-Manager: "
    
    Dim sq As Variant

    Open "C:\wigi.txt" For Input As #1
    s = Input(LOF(1), #1)
    Close #1
    
    sq = Filter(Split(s, vbCrLf), c0)
    Cells(1).Resize(UBound(sq) + 1) = Application.Transpose(sq)

End Sub

This code reads in the text file (change path and filename to test), extracts the lines with Co-Manager and outputs the results starting in cell A1 on the active sheet.

 

[Bercilak]

Thank you...working on your recommendation.