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.

 

[Yongle]

Re: Help VBA to find next value

Hi

What type of variable is ReadData ? If it's a range variable ... give this a try ...

If UBound(Split(ReadData, ";")) >= 1 Then  
    If InStr(1, Split(ReadData, ";")(0), "CODIGO") > 0 Then 
         Code = Split(ReadData[COLOR=#ff0000].Offset(1)[/COLOR], ";")(0)
    End If
End If

If it is not a range variable ... post the line which updates the value of variable ReadData

 

[1Ronin]

Re: Help VBA to find next value

Hi Yongle,


Is not working.
I have following code at beginning of macro:

Fpath = "C:\Users\nzw216\Desktop\Short\"

StrFile = Dir(Fpath)
Do While Len(StrFile) > 0
FilePath = Fpath & StrFile
StrFile = Dir
 
ReadData = ""
Rowc = Sheet1.Range("B" & Rows.Count).End(xlUp).Row


Open FilePath For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 




Serie = ""
i = 0
j = 0
X = 0
Do Until EOF(1)
 i = i + 1
 Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , ReadData

 

[Yongle]

Re: Help VBA to find next value

In that case try this
- delete the message box after testing

    If UBound(Split(ReadData, ";")) >= 1 Then
        If InStr(1, Split(ReadData, ";")(0), "CODIGO") > 0 Then
            Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , ReadData
            Code = Split(ReadData, ";")(0)
            MsgBox Code
        End If
    End If

 

[1Ronin]

Re: Help VBA to find next value

I try your solution and is working for "Code". I got correct code (23)

I try to use the same code for a similar situation (1 line below), but is not working there (result show is NUM OP). What I look for is NOOPER.
The TXT file look like this:

TIEMPO;02:44;
CODIGO;
23;MAX;
Pressure; Temp;
NUM OP;VALOR;REPARACIO SUGGERIDA;CODI REP;
NOOPER;MAXPAR;ND;ND;

The actual code is :

   '''Extragere Cod reject If UBound(Split(ReadData, ";")) >= 1 Then
  If InStr(1, Split(ReadData, ";")(0), "CODIGO") > 0 Then
            Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , ReadData
            Code = Split(ReadData, ";")(0)
  End If
 End If
 
 
    '''Extragere Num OP
 If UBound(Split(ReadData, ";")) >= 1 Then
  If InStr(1, Split(ReadData, ";")(0), "NUM OP") > 0 Then
           'Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , ReadData
            NumOp = Split(ReadData, ";")(0)
  End If
 End If

 

[Yongle]

Re: Help VBA to find next value

How many different strings are you looking for?

 

[1Ronin]

Re: Help VBA to find next value

In total or what is not working now?
In total macro extract more than 45 different values from each file.
At this moment this is the last parameter that is not working (NumOp).

 

[Yongle]

Re: Help VBA to find next value

I read your post on my phone earlier and did not spot your error - my eyes are old ...
Remove the apostrophe '

BAD

  If InStr(1, Split(ReadData, ";")(0), "NUM OP") > 0 Then
           [SIZE=4][B][COLOR=#ff0000]'[/COLOR][/B][/SIZE]Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL]  , ReadData
            NumOp = Split(ReadData, ";")(0)
  End If

GOOD

  If InStr(1, Split(ReadData, ";")(0), "NUM OP") > 0 Then
            Line Input [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL]  , ReadData
            NumOp = Split(ReadData, ";")(0)
  End If

 

[1Ronin]

Re: Help VBA to find next value

I try this before. Is giving an error ('62') and that line is in yellow.

"Run-time error '62':
Input past end of file"

Because of this I comment the line

 

[Yongle]

Re: Help VBA to find next value

I think we need to start again

In post#1 you said "At one point I need to find the value for "Code" which is a number" . I provided a solution.
Then you told me (in post#7) that "In total macro extract more than 45 different values from each file"

If you had said you were going to repeat the code many times I would have given you a different method
- one obvious problem is that the value of variable i is not correct
- there may be some other problems but I have not seen the whole of your code

Have used the code (provided in post#4) 45 times (once for each variable) ?