Read Text File and Copy Job Data

P

PingJu

New Member
Joined
Sep 21, 2017
Messages
8
Hello, I am trying to read a text (01202_WIP_DATA.txt) file with over 459,000 Rows of Data, The data is broken up into Project Numbers, with corresponding activity codes and costs. how can I have the User select the file to read, and then get data in VBA?
Then, On Worksheet("WIP") in Cells "A1" I will Manually insert a Job number (530004) that I want to pull data from the Txt file. (I will have roughly 40 of these down my spreadsheet) Row 38 will be my next project.
In Column "B4" thru "B30" , I will have Descriptions, and "C4" thru "C30" I will have Costs Codes: I want to pull the data for all of these cost Codes. Any Help would greatly be appreciated!

WIP.png


The TXT File Data:

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 1
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530004 01 - OFFICE PROJECT 1 PM1: 21781 Generic 1 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
199.00|INV UNDER $500 | | | | | | | | | 458| 458| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
EQUIPMENT :Subtotal | | | | | | | | 458| 458| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
245.00|Parts & Supplie| | | | | | | | | 52| 52| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC DUCT :Subtotal | | | | | | | | 52| 52| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
441.00|DDC Controls (C| | | | | | | | | 13,139| 13,139| 20,942|
464.00|Programming (Co| | | 40| | | | | | | | 2,480|
465.00|Commissioning | | | 172| | | | | | | | 8,600|
466.00|Training (Contr| | | 24| | | | | | | | 1,200|
468.00|Graphics | | | 40| | | | | | | | 2,000|
469.00|Supervision (Co| | 5.0 | 48| | | 64.43| | 322| 63| 385| 2,400|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SYS OPN/BAL :Subtotal | 5.0 | 324| | | 64.43| | 322| 13,202| 13,524| 37,622|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 324| | | 51.48| | 16,680| 20,942| 37,622| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 324| | | 51.48| | 16,680| 20,942| 37,622| 37,622|
| % Compl.| | | 1.5 % | | | | | 1.9 % | 63.0 % | 35.9 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
503.00|CONTROLS DESIGN| | 124.0 | 64| | | 56.59| | 7,018| 1,550| 8,568| 3,328|
520.00|PANEL ASSEMBLY | | 32.0 | 56| | | 53.77| | 1,721| 400| 2,121| 2,688|
599.00|Total Misc. Lab| | | | | | | | | | | 1,600|
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 2
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530004 01 - OFFICE PROJECT 1 PM1: 21781 Generic 1 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ENGINEERING :Subtotal | 156.0 | 120| | | 56.02| | 8,738| 1,950| 10,688| 7,616|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 120| | | 50.13| | 6,016| 1,600| 7,616| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 120| | | 50.13| | 6,016| 1,600| 7,616| 7,616|
| % Compl.| | | 130.0 % | | | | | 145.3 % | 121.9 % | 140.3 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
667.00|Freight & Trans| | | | | | | | | 112| 112| 6,785|
683.00|Miscellaneous | | | | | | | | | 557| 557| |
694.00|Sales / Use Tax| | | | | | | | | 1,297| 1,297| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
RENTAL/OTHER :Subtotal | | | | | | | | 1,966| 1,966| 6,785|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | 6,785| 6,785| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | 6,785| 6,785| 6,785|
| % Compl.| | | | | | | | | 29.0 % | 29.0 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
Total, Phase 1 | | | | | | | | | | | |
| Actual | 161.0 | 444| | | 56.28| | 9,061| 17,626| 26,687| 52,023|
| | | | | | | | | | | | |
| | | |<-------------- Budget --------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 444| | | 51.12| | 22,696| 29,327| 52,023| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 444| | | | | 22,696| 29,327| 52,023| |
| % Compl.| | | 36.3 % | | | | | 39.9 % | 60.1 % | 51.3 % | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 3
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
199.00|INV UNDER $500 | | | | | | | | | 2,595| 2,595| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
EQUIPMENT :Subtotal | | | | | | | | 2,595| 2,595| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
245.00|Parts & Supplie| | | | | | | | | -764| -764| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC DUCT :Subtotal | | | | | | | | -764| -764| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
441.00|DDC Controls (C| | | | | | | | | 99,271| 99,271| 109,854|
460.00|Install Control| | 2,948.0 | 995| | | 68.86| | 203,004| 36,916| 239,920| 79,600|
463.00|Controls TI (Co| | 4.0 | | | | 39.34| | 157| 50| 207| |
464.00|Programming (Co| | 87.5 | 88| | | 73.38| | 6,421| 1,094| 7,514| 7,040|
465.00|Commissioning | | 445.0 | 800| | | 50.30| | 22,384| 5,563| 27,947| 64,000|
466.00|Training (Contr| | | 8| | | | | | | | 640|
468.00|Graphics | 25.0| 168.0 | 80| | | 71.28| | 11,975| 2,100| 14,075| 6,400|
469.00|Supervision (Co| | 66.0 | 80| | | 81.42| | 5,373| 825| 6,198| 6,400|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SYS OPN/BAL :Subtotal 25.0| 3,718.5 | 2,051| | | 67.05| | 249,315| 145,818| 395,133| 273,934|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 1,920| | | 80.00| | 153,600| 95,000| 248,600| |
|APPROVED C/Os | | | 131| | | 80.00| | 10,480| 14,854| 25,334| |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 2,051| | | 80.00| | 164,080| 109,854| 273,934| 273,934|
| % Compl.| | | 181.3 % | | | | | 151.9 % | 132.7 % | 144.2 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
501.00|Design Engineer| | 4.0 | | | | 58.89| | 236| 50| 286| |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 4
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
503.00|CONTROLS DESIGN| | 16.0 | 80| | | 56.59| | 906| 200| 1,106| 6,400|
530.00|Project Enginee| 4.0| 43.0 | | | | 63.13| | 2,715| 538| 3,252| |
551.00|Project Manager| | | 40| | | | | | | | 4,000|
599.00|Total Misc. Lab| | | | | | | | | | | 3,000|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ENGINEERING :Subtotal 4.0| 63.0 | 120| | | 61.20| | 3,856| 788| 4,643| 13,400|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 120| | | 86.67| | 10,400| 3,000| 13,400| |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 120| | | 86.67| | 10,400| 3,000| 13,400| 13,400|
| % Compl.| | | 52.5 % | | | | | 37.1 % | 26.3 % | 34.7 % | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
618.00|Consulting Engi| | | | | | | | | 30| 30| |
628.00|MISC SUBCONTRAC| | | | | | | | | 2,425| 2,425| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
SUBCONTRACTS :Subtotal | | | | | | | | 2,455| 2,455| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
666.00|Rentals - Misce| | | | | | | | | 1,254| 1,254| |
667.00|Rentals, Freigh| | | | | | | | | 1,705| 1,705| |
683.00|Miscellaneous | | | | | | | | | 41| 41| |
694.00|Sales / Use Tax| | | | | | | | | 10,348| 10,348| |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
RENTAL/OTHER :Subtotal | | | | | | | | 13,347| 13,347| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
709.00|Miscellaneous | | 10.0 | | | | 41.32| | 413| 125| 538| |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 5
COST DIST B-4

* * * COST DISTRIBUTION REPORT * * *
For the Period 03/09/20 thru 03/15/20
Job/Phase: 530013 01 - OFFICE PROJECT 2 PM1: 21767 Generic2 PM2:
CONTROLS, SHELL & CORE, TI, LUMP SUM PM3: PM4:
===============================================================================================================================================
| |<------------ HOURS ----------->|<----- QUANTITY---->|AVERAGE COST |<--------------COST--------------->| |
ACTIVITY | DESCRIPTION | PERIOD| TOTAL | Budget | TOTAL |PER HOUR |LABOR | MATL | LABOR |Mtl & Other| *TOTAL | Budget |
==========|===============|=======|===========|============|==========|=========|======|======|===========|===========|===========|===========|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
MISC OTHER :Subtotal | 10.0 | | | | 41.32| | 413| 125| 538| |
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | | | | |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | | | | |
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
TOTAL |NCTC0220 | | | | | | | | | | | 142,801|
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
ADDTNL EST COST :Subtotal | | | | | | | | | | 142,801|
| | | | | | | | | | | | |
| | | |<------------- Budget ---------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | | | | | | | | | |
|APPROVED C/Os | | | | | | | | | | | |
|REVISIONS/PENDING | | | | | | | 16,000| 126,801| 142,801| |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | | | | | | 16,000| 126,801| 142,801| 142,801|
| % Compl.| | | | | | | | | | | |
| |-------|-----------|------------|----------|---------|------|------|-----------|-----------|-----------|-----------|
| | | | | | | | | | | | |
Total, Phase 1 | | | | | | | | | | | |
| Actual 29.0| 3,791.5 | 2,171| | | 66.88| | 253,584| 164,363| 417,947| 430,135|
| | | | | | | | | | | | |
| | | |<-------------- Budget --------------------------------------- Budget ------------>| |
|ORIGINAL BUDGET| | | 2,040| | | 80.39| | 164,000| 98,000| 262,000| |
|APPROVED C/Os | | | 131| | | | | 10,480| 14,854| 25,334| |
|REVISIONS/PENDING | | | | | | | 16,000| 126,801| 142,801| |
| | | |------------|----------|---------| | |-----------|-----------|-----------| |
| Total Budget | | 2,171| | | | | 190,480| 239,655| 430,135| |
| % Compl.| | | 174.6 % | | | | | 133.1 % | 68.6 % | 97.2 % | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |

SUNB Processed: 03/19/20 04:30:17 Selection List: ACTIVE JOB REPORT# 982 PAGE 6
COST DIST B-4
 
Sort by date Sort by votes
Try the following code with your report sample.txt

VBA Code: 
Sub read_txt_files()
  Dim ff As Long, i As Long, j As Long, lngFileLen As Long
  Dim arrLines As Variant, a As Variant, c As Variant
  Dim pickFile As String, strWholeFile As String, jobPhase As String, job1 As String, job2 As String
  Dim dic As Object, toval As Variant
  '
  With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select txt file"
    .Filters.Clear
    .Filters.Add "txt files", "*.txt"
    .AllowMultiSelect = False
    .InitialFileName = ThisWorkbook.Path & "\"
    If Not .Show Then Exit Sub
    pickFile = .SelectedItems.Item(1)
  End With
  '
  ff = FreeFile
  lngFileLen = FileLen(pickFile)
  strWholeFile = Space(lngFileLen)
  Open pickFile For Binary Access Read As #ff
    Get #ff, , strWholeFile
  Close #ff
  arrLines = Split(strWholeFile, vbCrLf)
  '
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("WIP").Range("A1:O" & Sheets("WIP").Range("A" & Rows.Count).End(3).Row + 40).Value2
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" And IsNumeric(a(i, 1)) Then
      dic("" & a(i, 1)) = i + 3
    End If
  Next
  '
  For i = 0 To UBound(arrLines)
    If arrLines(i) <> "" Then
      toval = Split(arrLines(i), "|")(0)
    Else
      toval = arrLines(i)
    End If
    If Left(toval, 10) = "Job/Phase:" Then
      jobPhase = arrLines(i)
      jobPhase = Application.Trim(jobPhase)
      job1 = Split(jobPhase, " ")(1)
      job2 = Mid(jobPhase, 11 + Len(job1) + 2, InStr(13, jobPhase, ":") - (11 + Len(job1) + 2))
      If dic.exists(job1) Then
        j = dic(job1) - 3
        If a(j, 2) = "" Then
          a(j, 2) = Left(job2, InStrRev(job2, " ") - 1)
          a(j, 3) = Mid(job2, InStrRev(job2, " ") + 1)
        End If
      End If
    ElseIf IsNumeric(toval) Then
      If dic.exists(job1) Then
        j = dic(job1)
        c = Split(arrLines(i), "|")
        a(j, 2) = c(1)
        a(j, 3) = c(0)
        a(j, 5) = c(2)
        a(j, 6) = c(3)
        a(j, 7) = c(4)
        a(j, 8) = c(5)
        a(j, 9) = c(6)
        a(j, 10) = c(7)
        a(j, 11) = c(8)
        a(j, 12) = c(9)
        a(j, 13) = c(10)
        a(j, 14) = c(11)
        a(j, 15) = c(12)
        dic(job1) = dic(job1) + 1
      End If
    End If
  Next
  '
  Sheets("WIP").Range("A1").Resize(UBound(a), 15).Value = a
End Sub
 
Upvote 0

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
You must log in or register to reply here.

Similar threads

T
  • Question
İmport Text File With Excel Vba
Replies
1
Views
2K
tevfikuzunn
T
S
  • Question
want to retrieve data from txt file into different fields...
Replies
4
Views
652
skalavati
S

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

Share this page

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
Back
Top