VBA to find next value

1Ronin

New Member
Joined
Aug 21, 2017
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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).

Code:
 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:mad:
Thank you.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Re: Help VBA to find next value

Hi

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

Code:
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
 
Last edited:
Upvote 0
Re: Help VBA to find next value

Hi Yongle,


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

Code:
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
 
Upvote 0
Re: Help VBA to find next value

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

Code:
    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
 
Upvote 0
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 :

Code:
   '''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
 
Upvote 0
Re: Help VBA to find next value

How many different strings are you looking for?
 
Upvote 0
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).
 
Upvote 0
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
Code:
  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
Code:
  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
 
Last edited:
Upvote 0
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:(
 
Upvote 0
Re: Help VBA to find next value

I think we need to start again :oops:

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) ?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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