VBA code to loop rows based on Row ID and copy values into columns

Question

I have raw field data text files from survey equipment i would like arranged in a summary table in a new worksheet ; i have created a code to import multiple text files by appending each file into a single table, however the raw data files contain too many rows not required in the summary. Out of approximately 1000 rows output in each text file, only 16 are needed for my summary table. The data is arranged based on each test No, therefore 'ROWIDs' in 'column 2' are identical for the metrics reported. I would like help creating macro to Loop through the raw data sheet based on 'column 1' as 'TestID' and 'column 2' as 'ROWID' then copy values in the 'column 4' into summary sheet, sample below, any help or questions are appreciated. Thank you

SAMPLE DATA BELOW;

Ontario2    5100    FILE NAME & SYSTEM MODEL              : Ontario2.sks  1295 PAVEMENT FRICTION TESTER (UNIVERSAL)     
Ontario2    5101    SOFTWARE VERSION                      : 1.0.6506.22298      
Ontario2    5101A   VCU SOFTWARE VERSION                 : 8000908.601.007a     
Ontario2    5102    TIME                       (hh:mm:ss) : 11:44:13        
Ontario2    5103    DATE                     (mm/dd/yyyy) : 01/22/2019      
Ontario2    0   =================================================================       
Ontario2    0    Test Summary Values                 Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    5000     Test Number                        1    
Ontario2    5001     Route                                               
Ontario2    5002     Direction                                           
Ontario2    5003     Lane                                                
Ontario2    5004     Event                                               
Ontario2    5005     Test Start DMI                     0    ft
Ontario2    5006     Test Start Time (Hr)               11   hr
Ontario2    5007     Test Start Time (Min)              40   minute
Ontario2    5008     Test Wheel                          Left            
Ontario2    5009     Test Wet/Dry                        WET             
Ontario2    5010     Latitude                            034.0564687° N  
Ontario2    5011     Longitude                           084.3002236° W  
Ontario2    5012     Bearing                            179.9    degrees
Ontario2    6000     Air Temperature                    -3.5     f
Ontario2    6001     Surface Temperature                55.6     f
Ontario2    6002     Left Tire Temperature               not installed   f
Ontario2    6003     Left Nozzle Water Temperature      0    f
Ontario2    6004     Right Tire Temperature              not installed   f
Ontario2    6005     Right Nozzle Water Temperature      not installed   f
Ontario2    6006     Tank 1 Water Temperature           0    f
Ontario2    6007     Tank 2 Water Temperature            not installed   f
Ontario2    6008     Tank 1 Water Level                 325.9    gal
Ontario2    6009     Tank 2 Water Level                  not installed   gal
Ontario2    6010     Data Filter                        5    points
Ontario2    6011     Water Depth                        0.5  mm
Ontario2    6012     Sampling Rate                      100  hz
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Right Wheel Peak Values             Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6020     Right Traction Force               0    lb
Ontario2    6021     Right Load Force                   0    lb
Ontario2    6022     Right Vehicle Speed                0    mph
Ontario2    6023     Right Test Wheel Speed             0    mph
Ontario2    6024     Right SN                           0    friction
Ontario2    6025     Right Percent Slip                 0    percent
Ontario2    6026     Right Time to Peak Brake Coef      0    seconds
Ontario2    6027     Right Time to Wheel Lockup         0    seconds
Ontario2    6028     Right Flow Rate                    0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Left Wheel Peak Values              Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6040     Left  Traction Force               1029.24  lb
Ontario2    6041     Left  Load Force                   949.52   lb
Ontario2    6042     Left  Vehicle Speed                37.5     mph
Ontario2    6043     Left  Test Wheel Speed             33.1     mph
Ontario2    6044     Left  SN                           108.4    friction
Ontario2    6045     Left  Percent Slip                 11.6     percent
Ontario2    6046     Left  Time to Peak Brake Coef      1.13     seconds
Ontario2    6047     Left  Time to Wheel Lockup         1.37     seconds
Ontario2    6048     Left  Flow Rate                    0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Right Wheel SN Values               Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6060     Right SN Average                   0    friction
Ontario2    6061     Right SN Minimum                   0    friction
Ontario2    6062     Right SN Maximum                   0    friction
Ontario2    6063     Right SN Standard Deviation        0    friction
Ontario2    6064     Right SN Speed Average             0    mph
Ontario2    6065     Right SN Flow Average              0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Left Wheel SN Values                Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6080     Left  SN Average                   84.2     friction
Ontario2    6081     Left  SN Minimum                   78.3     friction
Ontario2    6082     Left  SN Maximum                   89.4     friction
Ontario2    6083     Left  SN Standard Deviation        2.7  friction
Ontario2    6084     Left  SN Speed Average             36.6     mph
Ontario2    6085     Left  SN Flow Average              0    gpm
Ontario2    0   =================================================================       
Ontario2    XXXX    ^^^^ Speed ^^^^     
Ontario2    0   =================================================================       
Ontario2    0    Test Summary Values                 Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    5000     Test Number                        2    
Ontario2    5001     Route                                               
Ontario2    5002     Direction                                           
Ontario2    5003     Lane                                                
Ontario2    5004     Event                                               
Ontario2    5005     Test Start DMI                     29.36    ft
Ontario2    5006     Test Start Time (Hr)               11   hr
Ontario2    5007     Test Start Time (Min)              41   minute
Ontario2    5008     Test Wheel                          Left            
Ontario2    5009     Test Wet/Dry                        WET             
Ontario2    5010     Latitude                            034.0511896° N  
Ontario2    5011     Longitude                           084.3033369° W  
Ontario2    5012     Bearing                            201.9    degrees
Ontario2    6000     Air Temperature                    -1.6     f
Ontario2    6001     Surface Temperature                49.9     f
Ontario2    6002     Left Tire Temperature               not installed   f
Ontario2    6003     Left Nozzle Water Temperature      0    f
Ontario2    6004     Right Tire Temperature              not installed   f
Ontario2    6005     Right Nozzle Water Temperature      not installed   f
Ontario2    6006     Tank 1 Water Temperature           0    f
Ontario2    6007     Tank 2 Water Temperature            not installed   f
Ontario2    6008     Tank 1 Water Level                 353.3    gal
Ontario2    6009     Tank 2 Water Level                  not installed   gal
Ontario2    6010     Data Filter                        5    points
Ontario2    6011     Water Depth                        0.5  mm
Ontario2    6012     Sampling Rate                      100  hz
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Right Wheel Peak Values             Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6020     Right Traction Force               0    lb
Ontario2    6021     Right Load Force                   0    lb
Ontario2    6022     Right Vehicle Speed                0    mph
Ontario2    6023     Right Test Wheel Speed             0    mph
Ontario2    6024     Right SN                           0    friction
Ontario2    6025     Right Percent Slip                 0    percent
Ontario2    6026     Right Time to Peak Brake Coef      0    seconds
Ontario2    6027     Right Time to Wheel Lockup         0    seconds
Ontario2    6028     Right Flow Rate                    0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Left Wheel Peak Values              Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6040     Left  Traction Force               880.6    lb
Ontario2    6041     Left  Load Force                   959.17   lb
Ontario2    6042     Left  Vehicle Speed                40.5     mph
Ontario2    6043     Left  Test Wheel Speed             36   mph
Ontario2    6044     Left  SN                           91.81    friction
Ontario2    6045     Left  Percent Slip                 11.2     percent
Ontario2    6046     Left  Time to Peak Brake Coef      0.62     seconds
Ontario2    6047     Left  Time to Wheel Lockup         0.76     seconds
Ontario2    6048     Left  Flow Rate                    29.1     gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Right Wheel SN Values               Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6060     Right SN Average                   0    friction
Ontario2    6061     Right SN Minimum                   0    friction
Ontario2    6062     Right SN Maximum                   0    friction
Ontario2    6063     Right SN Standard Deviation        0    friction
Ontario2    6064     Right SN Speed Average             0    mph
Ontario2    6065     Right SN Flow Average              0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Left Wheel SN Values                Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6080     Left  SN Average                   53.4     friction
Ontario2    6081     Left  SN Minimum                   51.5     friction
Ontario2    6082     Left  SN Maximum                   55.5     friction
Ontario2    6083     Left  SN Standard Deviation        0.9  friction
Ontario2    6084     Left  SN Speed Average             40   mph
Ontario2    6085     Left  SN Flow Average              28.8     gpm
Ontario2    0   =================================================================       
Ontario2    0   =================================================================       
Ontario2    0    Test Summary Values                 Value           Units
Ontario2    0   -----------------------------------------------------------------       

Ontario2    5000     Test Number                        12   
Ontario2    5001     Route                                               
Ontario2    5002     Direction                                           
Ontario2    5003     Lane                                                
Ontario2    5004     Event                                               
Ontario2    5005     Test Start DMI                     8053.43  ft
Ontario2    5006     Test Start Time (Hr)               11   hr
Ontario2    5007     Test Start Time (Min)              44   minute
Ontario2    5008     Test Wheel                          Left            
Ontario2    5009     Test Wet/Dry                        WET             
Ontario2    5010     Latitude                            034.0483463° N  
Ontario2    5011     Longitude                           084.3183640° W  
Ontario2    5012     Bearing                            41.7     degrees
Ontario2    6000     Air Temperature                    -1.6     f
Ontario2    6001     Surface Temperature                47.6     f
Ontario2    6002     Left Tire Temperature               not installed   f
Ontario2    6003     Left Nozzle Water Temperature      0    f
Ontario2    6004     Right Tire Temperature              not installed   f
Ontario2    6005     Right Nozzle Water Temperature      not installed   f
Ontario2    6006     Tank 1 Water Temperature           0    f
Ontario2    6007     Tank 2 Water Temperature            not installed   f
Ontario2    6008     Tank 1 Water Level                 326  gal
Ontario2    6009     Tank 2 Water Level                  not installed   gal
Ontario2    6010     Data Filter                        5    points
Ontario2    6011     Water Depth                        0.5  mm
Ontario2    6012     Sampling Rate                      100  hz
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Right Wheel Peak Values             Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6020     Right Traction Force               0    lb
Ontario2    6021     Right Load Force                   0    lb
Ontario2    6022     Right Vehicle Speed                0    mph
Ontario2    6023     Right Test Wheel Speed             0    mph
Ontario2    6024     Right SN                           0    friction
Ontario2    6025     Right Percent Slip                 0    percent
Ontario2    6026     Right Time to Peak Brake Coef      0    seconds
Ontario2    6027     Right Time to Wheel Lockup         0    seconds
Ontario2    6028     Right Flow Rate                    0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Left Wheel Peak Values              Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6040     Left  Traction Force               825.19   lb
Ontario2    6041     Left  Load Force                   946.42   lb
Ontario2    6042     Left  Vehicle Speed                40.6     mph
Ontario2    6043     Left  Test Wheel Speed             37.2     mph
Ontario2    6044     Left  SN                           87.19    friction
Ontario2    6045     Left  Percent Slip                 8.6  percent
Ontario2    6046     Left  Time to Peak Brake Coef      0.59     seconds
Ontario2    6047     Left  Time to Wheel Lockup         0.71     seconds
Ontario2    6048     Left  Flow Rate                    29.3     gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Right Wheel SN Values               Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6060     Right SN Average                   0    friction
Ontario2    6061     Right SN Minimum                   0    friction
Ontario2    6062     Right SN Maximum                   0    friction
Ontario2    6063     Right SN Standard Deviation        0    friction
Ontario2    6064     Right SN Speed Average             0    mph
Ontario2    6065     Right SN Flow Average              0    gpm
Ontario2    0   -----------------------------------------------------------------       
Ontario2    0    Left Wheel SN Values                Value           Units
Ontario2    0   -----------------------------------------------------------------       
Ontario2    6080     Left  SN Average                   51.5     friction
Ontario2    6081     Left  SN Minimum                   47   friction
Ontario2    6082     Left  SN Maximum                   54.8     friction
Ontario2    6083     Left  SN Standard Deviation        1.8  friction
Ontario2    6084     Left  SN Speed Average             40.2     mph
Ontario2    6085     Left  SN Flow Average              28.9     gpm
Ontario2    0   =================================================================       
Ontario2    0   =================================================================       
Ontario3 - Copy (2) 5100    FILE NAME & SYSTEM MODEL              : Ontario3.sks  1295 PAVEMENT FRICTION TESTER (UNIVERSAL)     
Ontario3 - Copy (2) 5101    SOFTWARE VERSION                      : 1.0.6506.22298      
Ontario3 - Copy (2) 5101A   VCU SOFTWARE VERSION                 : 8000908.601.007a     
Ontario3 - Copy (2) 5102    TIME                       (hh:mm:ss) : 11:44:13        
Ontario3 - Copy (2) 5103    DATE                     (mm/dd/yyyy) : 01/22/2019      
Ontario3 - Copy (2) 0   =================================================================       
Ontario3 - Copy (2) 0    Test Summary Values                 Value           Units
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 5000     Test Number                        1    
Ontario3 - Copy (2) 5001     Route                                               
Ontario3 - Copy (2) 5002     Direction                                           
Ontario3 - Copy (2) 5003     Lane                                                
Ontario3 - Copy (2) 5004     Event                                               
Ontario3 - Copy (2) 5005     Test Start DMI                     0    ft
Ontario3 - Copy (2) 5006     Test Start Time (Hr)               11   hr
Ontario3 - Copy (2) 5007     Test Start Time (Min)              40   minute
Ontario3 - Copy (2) 5008     Test Wheel                          Left            
Ontario3 - Copy (2) 5009     Test Wet/Dry                        WET             
Ontario3 - Copy (2) 5010     Latitude                            034.0564687° N  
Ontario3 - Copy (2) 5011     Longitude                           084.3002236° W  
Ontario3 - Copy (2) 5012     Bearing                            179.9    degrees
Ontario3 - Copy (2) 6000     Air Temperature                    -3.5     f
Ontario3 - Copy (2) 6001     Surface Temperature                55.6     f
Ontario3 - Copy (2) 6002     Left Tire Temperature               not installed   f
Ontario3 - Copy (2) 6003     Left Nozzle Water Temperature      0    f
Ontario3 - Copy (2) 6004     Right Tire Temperature              not installed   f
Ontario3 - Copy (2) 6005     Right Nozzle Water Temperature      not installed   f
Ontario3 - Copy (2) 6006     Tank 1 Water Temperature           0    f
Ontario3 - Copy (2) 6007     Tank 2 Water Temperature            not installed   f
Ontario3 - Copy (2) 6008     Tank 1 Water Level                 325.9    gal
Ontario3 - Copy (2) 6009     Tank 2 Water Level                  not installed   gal
Ontario3 - Copy (2) 6010     Data Filter                        5    points
Ontario3 - Copy (2) 6011     Water Depth                        0.5  mm
Ontario3 - Copy (2) 6012     Sampling Rate                      100  hz
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 0    Right Wheel Peak Values             Value           Units
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 6020     Right Traction Force               0    lb
Ontario3 - Copy (2) 6021     Right Load Force                   0    lb
Ontario3 - Copy (2) 6022     Right Vehicle Speed                0    mph
Ontario3 - Copy (2) 6023     Right Test Wheel Speed             0    mph
Ontario3 - Copy (2) 6024     Right SN                           0    friction
Ontario3 - Copy (2) 6025     Right Percent Slip                 0    percent
Ontario3 - Copy (2) 6026     Right Time to Peak Brake Coef      0    seconds
Ontario3 - Copy (2) 6027     Right Time to Wheel Lockup         0    seconds
Ontario3 - Copy (2) 6028     Right Flow Rate                    0    gpm
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 0    Left Wheel Peak Values              Value           Units
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 6040     Left  Traction Force               1029.24  lb
Ontario3 - Copy (2) 6041     Left  Load Force                   949.52   lb
Ontario3 - Copy (2) 6042     Left  Vehicle Speed                37.5     mph
Ontario3 - Copy (2) 6043     Left  Test Wheel Speed             33.1     mph
Ontario3 - Copy (2) 6044     Left  SN                           108.4    friction
Ontario3 - Copy (2) 6045     Left  Percent Slip                 11.6     percent
Ontario3 - Copy (2) 6046     Left  Time to Peak Brake Coef      1.13     seconds
Ontario3 - Copy (2) 6047     Left  Time to Wheel Lockup         1.37     seconds
Ontario3 - Copy (2) 6048     Left  Flow Rate                    0    gpm
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 0    Right Wheel SN Values               Value           Units
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 6060     Right SN Average                   0    friction
Ontario3 - Copy (2) 6061     Right SN Minimum                   0    friction
Ontario3 - Copy (2) 6062     Right SN Maximum                   0    friction
Ontario3 - Copy (2) 6063     Right SN Standard Deviation        0    friction
Ontario3 - Copy (2) 6064     Right SN Speed Average             0    mph
Ontario3 - Copy (2) 6065     Right SN Flow Average              0    gpm
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 0    Left Wheel SN Values                Value           Units
Ontario3 - Copy (2) 0   -----------------------------------------------------------------       
Ontario3 - Copy (2) 6080     Left  SN Average                   84.2     friction
Ontario3 - Copy (2) 6081     Left  SN Minimum                   78.3     friction
Ontario3 - Copy (2) 6082     Left  SN Maximum                   89.4     friction
Ontario3 - Copy (2) 6083     Left  SN Standard Deviation        2.7  friction
Ontario3 - Copy (2) 6084     Left  SN Speed Average             36.6     mph
Ontario3 - Copy (2) 6085     Left  SN Flow Average              0    gpm
Ontario3 - Copy (2) 0   =================================================================       
Ontario3 - Copy (2) XXXX    ^^^^ Speed ^^^^     
Ontario3 - Copy (2) 0   =================================================================       
Ontario3 - Copy (2) 0    Test Summary Values                 Value           Units
Ontario3 - Copy (2) 0   -----------------------------------------------------

Expected output summary table;

Test_ID Test    Distance    Time    Wheel   WetDry  Speed   AvgFN   MinFN   MaxFN   StDevFN Temp    GPS_Lat GPS_Long    Notes   TestDate
Ontario2    1   0   11:40    Left            WET            36.6    84.2    78.3    89.4    2.7 55.6     034.0564687° N  084.3002236° W     1/22/2019
Ontario2    2   29.36   11:41    Left            WET            40  53.4    51.5    55.5    0.9 49.9     034.0511896° N  084.3033369° W     1/22/2019

You can have a look at the answer (https://stackoverflow.com/questions/24377197/iterating-through-populated-rows-in-excel-using-vba) — Mikku, Jun 07 '19 at 06:36
Welcome to StackOverflow Gideon, what have you tried so far? Did you do a little bit of research by yourself? Note that this is not a free coding service, we can help you with your coding problems, but you need to show some code and where are you errors. — Damian, Jun 07 '19 at 06:37

Ahmed AU · Accepted Answer · 2019-06-09T07:10:55.790

Here is a skeleton code for the purpose, may modify to your requirement. Considered the input text file as fixed width with multiple space. Tested with sample data provided. Output table structure was modified for simplicity as follows.

Code directly process the text file

Sub test()
Dim Fno As Integer, Ln As String, Arr As Variant
Dim TestNo As String, TestID As String, Wheel As String
Dim Rw As Long
Dim Ws As Worksheet
Dim MyFile As Variant

Set Ws = ThisWorkbook.ActiveSheet

With Application.FileDialog(msoFileDialogFilePicker)
   .AllowMultiSelect = True
   .Filters.Add "Text", "*.Txt", 1

    If .Show <> -1 Then Exit Sub
    Rw = 6

    For Each MyFile In .SelectedItems
    Fno = FreeFile

    Open MyFile For Input As #Fno

    TestID = ""
    TestNo = ""
    Wheel = ""

        Do While Not EOF(Fno)    ' Loop until end of file.
        Line Input #Fno, Ln    ' Read line into variable.
        Ln = Trim(Ln)

        ' multiple spaces are converted to single space for splitting
            Do Until InStr(1, Ln, "  ") = 0
            Ln = Replace(Ln, "  ", " ")
            Arr = Split(Ln, " ")
            Loop

            If UBound(Arr) > 2 Then
                Select Case Arr(1)
                Case "5000"        'TestNo
                    If TestID <> Arr(0) Or TestNo <> Arr(UBound(Arr)) Then
                    Rw = Rw + 1
                    TestID = Arr(0)
                    TestNo = Arr(UBound(Arr))
                    Ws.Cells(Rw, 1).Value = TestID
                    Ws.Cells(Rw, 2).Value = TestNo
                    End If
                Case "5005"   'Distance
                Ws.Cells(Rw, 3).Value = Arr(UBound(Arr) - 1)
                Case "5006"   'Start Hr
                Ws.Cells(Rw, 4).Value = Arr(UBound(Arr) - 1)
                Case "5007"    'Start min
                Ws.Cells(Rw, 5).Value = Arr(UBound(Arr) - 1)
                Case "5008"    'Wheel
                Wheel = Arr(UBound(Arr))
                Ws.Cells(Rw, 6).Value = Arr(UBound(Arr))
                Case "5009"    'WetDry
                Ws.Cells(Rw, 7).Value = Arr(UBound(Arr))
                Case "5010"    'Lat
                Ws.Cells(Rw, 14).Value = Arr(UBound(Arr) - 1) & " " & Arr(UBound(Arr))
                Case "5011"    'Long
                Ws.Cells(Rw, 15).Value = Arr(UBound(Arr) - 1) & " " & Arr(UBound(Arr))
                Case "6001"    'Surface Temp
                Ws.Cells(Rw, 13).Value = Arr(UBound(Arr) - 1)
                End Select

                If Wheel = "Right" Then
                    Select Case Arr(1)
                    Case "6060"          'SN Average
                    Ws.Cells(Rw, 9).Value = Arr(UBound(Arr) - 1)
                    Case "6061"          'SN Min
                    Ws.Cells(Rw, 10).Value = Arr(UBound(Arr) - 1)
                    Case "6062"          'SN MAx
                    Ws.Cells(Rw, 11).Value = Arr(UBound(Arr) - 1)
                    Case "6063"          'SN St Dev
                    Ws.Cells(Rw, 12).Value = Arr(UBound(Arr) - 1)
                    Case "6064"          'Speed Avg
                    Ws.Cells(Rw, 8).Value = Arr(UBound(Arr) - 1)
                    End Select
                Else
                    Select Case Arr(1)
                    Case "6080"          'SN Average
                    Ws.Cells(Rw, 9).Value = Arr(UBound(Arr) - 1)
                    Case "6081"          'SN Min
                    Ws.Cells(Rw, 10).Value = Arr(UBound(Arr) - 1)
                    Case "6082"          'SN MAx
                    Ws.Cells(Rw, 11).Value = Arr(UBound(Arr) - 1)
                    Case "6083"          'SN St Dev
                    Ws.Cells(Rw, 12).Value = Arr(UBound(Arr) - 1)
                    Case "6084"          'Speed Avg
                    Ws.Cells(Rw, 8).Value = Arr(UBound(Arr) - 1)
                    End Select
            End If
            End If
        Loop
Close #Fno

Next MyFile

End With

End Sub

This is brilliant @Ahamed AU, tested code on the sample txt file and it worked like charm! Now if i wanted to use this code to copy multiple text files in a folder and append to one worksheet, How do i modify the input, so that i don't have to modify the path each time? Much appreciated! — Gideon G, Jun 09 '19 at 06:32
Edited answer for Selecting multiple text files. Don't think copying or appending is necessary. Hope it will solve problem. if it solved kindly accept the answer by clicking accept tick mark. — Ahmed AU, Jun 09 '19 at 07:14
Hi @Ahamed AU this solved my problem, I just added code to first export txt with fixed width columns for uniformity, thank you for your prompt attention and resolving this so quickly! — Gideon G, Jun 10 '19 at 00:56
@Ahamed AU , i'm gonna ask for your help again, this code works just fine, thank you; now some text files format is different, missing column one which contains the txt file name (without .txt) when the txt file above is missing the first column, how do i modify your code above to insert the file name in column1? Also the file name is under first row RowID '5100' of every txt file. My apologies if this is similar to my other question i posted. — Gideon G, Jun 14 '19 at 02:09
I still could clearly get your goal. If want data to be extracted from the changed input file formats ( as given in new post) to the output format of this question. then edit your new question and and add the output format. — Ahmed AU, Jun 14 '19 at 16:59
Hi @Ahamed AU, I have modified the new question,i believe your magic code above will work with some modification, sorry for any confusion caused. — Gideon G, Jun 14 '19 at 19:12

VBA code to loop rows based on Row ID and copy values into columns

1 Answers1