VBA to find next value

[1Ronin]

Hello,

I have a bigger macro that open many TXT files one by one, search for different values and
copy after in a data sheet. At one point I need to find the value for "Code" which is a number.
If TXT file is opened by Excel, this value is in column A and the position of row can be different from file to file. But, is always after word "CODIGO".
An partial example of TXT file is below:

TIEMPO;01:53;
CODIGO;
23;MAX;

I have below code, which is used with some modifications in other parts of macro for different parameters, but obviously is not good for me. This code is giving always the result "CODIGO". I need to find next value (23).

 If UBound(Split(ReadData, ";")) >= 1 Then  If InStr(1, Split(ReadData, ";")(0), "CODIGO") > 0 Then
    
   Code = Split(ReadData, ";")(0)
   
  End If
 End If

Please help me to find correct value. I know that is easy, but not for me
Thank you.

 

[1Ronin]

There are lines where VBA cannot evaluate CDbl(Split(ReadData, ";")(19))

If UBound(Split(ReadData, ";")) [COLOR=#ff0000]>= 18 [/COLOR]Then
[COLOR=#ff0000]is not consistent with[/COLOR]
OP915 = CDbl(Split(ReadData, ";")([COLOR=#ff0000]19[/COLOR]))

Try

If UBound(Split(ReadData, ";"))[COLOR=#ff0000] > 18[/COLOR] Then

(In original code, CDbl(Split(ReadData, ";")(19)) was only evaluated after a second test for each value which prevented the original code failing)

Is giving identical error, same line.

 

[Yongle]

OK
- this will convert those values which can be converted and skip over those values that cannot be converted to double
- all the values being used in the code are type Double

Replace this line:

ReadData19 = CDbl(Split(ReadData, ";")(19))

With these:

  ReadData19 = Split(ReadData, ";")(19)
On Error Resume Next
  ReadData19 = CDbl(ReadData19)
On Error GoTo 0

 

[1Ronin]

Now is working, but not perfect.
Still issues in column for NumOp. The actual result is:

[TABLE="width: 164"]
<tbody>[TR]
[TD]Code[/TD]
[TD]NumOp[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]NOOPER[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]NOOPER[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]BENCH:[/TD]
[/TR]
</tbody>[/TABLE]

- First result is OK: error code 0 -> NumOp = nothing (or 0)
- Where is BENCH (this is the value from cell A1) should be also nothing (or 0)
- Where is a code (here is 10, but could be other number) -> could be Nooper or another number associated with this code, but not 0

I think now that another way to find the value for NumOp (empty cell in case of good test / or a number in case of fail) is that this is located always on last row in column A. Maybe this is helping...

 

[Yongle]

I can only help if you tell me where to find the value in ReadData and also what the condition(s) should be

Do we need to add a condition to test the value of Bench before trying to find value of NumOp ?

Do we need to test for any other value ?

Where is the correct value for NumOp found?
- is it always in the next line (that is where VBA is looking)
- is it always here Split(ReadData, ";")(0)

 

[1Ronin]

Hi again,


When I talk about "test" is about my text files, which are the results of test for some products. Each txt file represent the test results for one product.
We don't need to make any test in the VBA.


Regarding the position of NumOp value, this can be found in TXT file as I say before:

TIEMPO;02:44;
CODIGO;
23;MAX;
Pressure; Temp;
NUM OP;VALOR;REPARACIO SUGGERIDA;CODI REP;
NOOPER;MAXPAR;ND;ND; (comment - this is the last row of my file and the code "NOOPER" is in the first position on row)


I hope is more clear now. Sorry for any confusion created.

 

[1Ronin]

OK
- this will convert those values which can be converted and skip over those values that cannot be converted to double
- all the values being used in the code are type Double

Replace this line:

ReadData19 = CDbl(Split(ReadData, ";")(19))

With these:

  ReadData19 = Split(ReadData, ";")(19)
On Error Resume Next
  ReadData19 = CDbl(ReadData19)
On Error GoTo 0

Hello again,
The macro is working almost Ok, but has two errors. Normally I use another macro, only for low number of files, because is very slow, less than 2 files/sec. This one is very very fast, so try I use only for large number of files to be processed (more than 2-3000files), but this is happened rarely. That's why I didn't see the second error till now...
1) First is the one above, with issues in column for NumOp. This was solved by me using an additional macro that makes correction in the columns Code and NumOp.
2) The second error is not solved and will try to explain below:
- Let's say that I have 10 files to extract test results: 9 tests are pass and 1 test is fail.
- The test plan I use has let's say 20 parameters to be measured and should extracted by macro
- For all 9 tests passed macro will give me 20 values/file
- For 1 test fail (due to various reasons) I will have less values than 20. Let's say we have only 5 parameters recorded, because test was stopped.
- Now if the FAIL test is the first file to be processed, I have only 5 results and the rest of cells till 20 are empty
- If the FAIL test is not the first processed, the macro will fill the cells with the 5 results and also the rest till 20 with results from previous file. This is giving me wrong data...

I upload also an image where you can see the issue. Red numbers are doubled.
Is like the macro don't find all expected data and fill with results from previous file. This is not Ok.
Sorry for large post.
Many thanks in advance for any help.

Regards.