Code running very slow

I am running the below code to read data from several files, find key properties and write them to a Sheet. There are 54 very long files (e.g., 12,000 lines), but I don't think this should run so slow. I've identified the section of code below that I think is slowing it down. I hope someone can help me figure this out.

Please Login or Register  to view this content.

As Slow is the Excel surname ! Better is to use any database software …

Anyway do not forget to set ScreenUpdating to False before writting to cells ! (codeline in comment, why ?‼)

Thanks. I added more comments to the code and include an example of one of the source files. Basically, I first reverse the order of rows in the source file and put the results in Sheet3.
Then for each file listed in Sheet2, I examine each row to look for specific threads in the text. When I find one that I am looking for, I write parts of that line to Sheet1.
The input files were given to me in .CNV format, which looks like text lines in an Excel sheet. When I try to upload one of the files (or even a portion of one),
I get a message saying it is invalid. So, below the code, I have included enough lines from the file to show what they look like. I hope that helps some.

Please Login or Register  to view this content.

THIS IS WHAT THE INPUT FILES LOOK LIKE
* Sea-Bird SBE25 Data File:
* FileName = \\LGDC01\01_MT_Data\01_Sensor_Data\SBE25\2012\Erie Do July 17\July 17 DO -All casts separated by cast-004.hex
* Software Version 1.59
* Temperature SN = 031554
* Conductivity SN = 042244
* System UpLoad Time = Sep 20 2012 15:52:00
** Lake Erie DO July 17 2012
** All casts separated by cast (cast 4)
* ds
* SBE 25 CTD V 4.0b SN 111 09/20/12 11:39:41.012
* external pressure sensor, range = 1000 psia, tcval = 181
* xtal = 9437443 clk = 32768.342 vmain = 12.6 iop = 170 vlith = 5.4
* ncasts = 15 samples = 30246 free = 74216 lwait = 0 msec
*
* CTD configuration:
* number of scans averaged = 1, data stored at 8 scans per second
* real time data transmitted at 8 scans per second
* minimum conductivity frequency for pump turn on = 2644
* pump delay = 30 seconds
*
* battery type = ALKALINE
*
* 1 external voltages sampled
* stored voltage # 0 = external voltage 0
*

* S>
* dh
* cast 4 07/25 08:32:33 smpls 6924 to 10404 nv = 1 avg = 1 stp = switch of

* S>
# nquan = 4
# nvalues = 3481
# units = specified
# name 0 = depFM: Depth [fresh water, m]
# name 1 = t090C: Temperature [ITS-90, deg C]
# name 2 = sbeox0Mg/L: Oxygen, SBE 43 [mg/l]
# name 3 = flag: 0.000e+00
# span 0 = -0.636, 23.395
# span 1 = 13.2216, 25.8660
# span 2 = 1.61425, 9.09414
# span 3 = 0.0000e+00, 0.0000e+00
# interval = seconds: 0.125
# start_time = Jul 25 2012 08:32:33 [Instrument's time stamp, header]
# bad_flag = -9.990e-29
# <Sensors count="4" >
# <sensor Channel="1" >
# <!-- Frequency 0, Temperature -->
# <TemperatureSensor SensorID="55" >
# <SerialNumber>031554</SerialNumber>
# <CalibrationDate>25-Jan-12</CalibrationDate>
# <UseG_J>1</UseG_J>
# <A>0.00000000e+000</A>
# <B>0.00000000e+000</B>
# <C>0.00000000e+000</C>
# <D>0.00000000e+000</D>
# <F0_Old>0.000</F0_Old>
# <G>4.85050923e-003</G>
# <H>6.81902510e-004</H>
# <I>2.74285429e-005</I>
# <J>2.21584767e-006</J>
# <F0>1000.000</F0>
# <Slope>1.00000000</Slope>
# <Offset>0.0000</Offset>
# </TemperatureSensor>
# </sensor>
# <sensor Channel="2" >
# <!-- Frequency 1, Conductivity -->
# <ConductivitySensor SensorID="3" >
# <SerialNumber>042244</SerialNumber>
# <CalibrationDate>17-Jan-12</CalibrationDate>
# <UseG_J>1</UseG_J>
# <!-- Cell const and series R are applicable only for wide range sensors. -->
# <SeriesR>0.0000</SeriesR>
# <CellConst>2000.0000</CellConst>
# <ConductivityType>0</ConductivityType>
# <Coefficients equation="0" >
# <A>0.00000000e+000</A>
# <B>0.00000000e+000</B>
# <C>0.00000000e+000</C>
# <D>0.00000000e+000</D>
# <M>0.0</M>
# <CPcor>-9.57000000e-008</CPcor>
# </Coefficients>
# <Coefficients equation="1" >
# <G>-1.02770178e+001</G>
# <H>1.47451413e+000</H>
# <I>-6.23325241e-004</I>
# <J>1.23463596e-004</J>
# <Tcor>0.0000</Tcor>
# </OxygenSensor>
# </sensor>
# </Sensors>
# datcnv_date = Sep 27 2012 14:00:44, 7.21f [datcnv_vars = 3]
# datcnv_in = C:\EPAWork\Lake Erie DO\Erie DO\Erie 12\July 17-18\ER36 7-25 (25).hex C:\EPAWork\Lake Erie DO\Erie DO\Erie 12\July 17-18\SBE 25 060512.xmlcon
# datcnv_skipover = 0
# datcnv_ox_hysteresis_correction = yes
# datcnv_ox_tau_correction = yes
# file_type = ascii
*END*
-0.539 25.7811 8.23811 0.000e+00
-0.539 25.7744 8.24493 0.000e+00
-0.636 25.7666 8.24674 0.000e+00
-0.636 25.7616 8.24812 0.000e+00
-0.636 25.7612 8.24869 0.000e+00
-0.636 25.7572 8.24972 0.000e+00
-0.636 25.7522 8.25079 0.000e+00
-0.636 25.7486 8.25155 0.000e+00
-0.636 25.7468 8.25193 0.000e+00
-0.636 25.7450 8.24640 0.000e+00
-0.636 25.7401 8.25213 0.000e+00
-0.636 25.7347 8.24705 0.000e+00
-0.636 25.7339 8.25206 0.000e+00
-0.636 25.7339 8.25186 0.000e+00
-0.636 25.7318 8.25188 0.000e+00

It would not allow me to upload a .CNV file so I opened and saved it as .xlsx it is now attached

Thanks

Here is the .xlxm file with all of the information. Sorry this is problematic. I am not very experienced with VBA!

Originally Posted by scavia

It would not allow me to upload a .CNV file so I opened and saved it as .xlsx

Well, well, well ! As it is very not difficult to rename a couple of source text files as .txt !
For example File1.cnv as File1.cnv.txt and File2.cnv as File2.cnv.txt then attach both in this thread …

I just tried that again. Opened file.cnv and saved as file.cnv.txt. When I tried to upload it, it said "upload of file failed". So, I renamed file.cnv.txt to file.txt
and that failed too. Strange.

Tried using 7-zip which is on my PC. the file.7z upload also failed. Maybe I'm just wasting your time now?

Yes, I want to do that before looking at the individual rows because the order matters

Mumps1 - I wanted to close this loop with you because I found my problem. I was looping inside too many loops as you pointed out in the beginning. I really appreciate the help.