OK, first things first. I'm not the best coder so the code below is a little bit cumbersome but I hope it get's you going.
- Open your Visual Basic Editor with the shortcut
Alt+F11. When you have a laptop you probably should give the shortcut
Fn+Alt+F11. The Fn button on a laptop is called the function key and is used to activate a second function on the top buttons.
- In the top menubar click
Insert and then choose
Module.
- Copy and Paste the VBA code wich you can find below.
- To activate the red part:
'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime
click in the top menubar
Tools and then
References. Scroll through the very long list and search for "Microsoft Scripting Runtime" check the checkbox and click
OK.
- Make adjustments to the path name in this line:
Set txtStream = fspenTextFile("c:\temp\textfile.txt", ForReading, False). Point to the location where your text file is and you want to search.
- Return to your workbook.
Now you have to realize that we don't see your setup. How that looks like in your workbook. So we have to guess. You say:
"So what i need to do is to search that identification number and calculate the exact tax for that person. Each state gives you a LARGE txt file with millons of "identification numbers".
Now let's assume that you have a bunch of your identification numbers in
Column A of
Sheet1 and run the code.
Excel 2016 Professional (Windows) 64 bit
| [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR] |
---|
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR] | Identification_Numbers |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR] | 20000163989 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR] | 20000164626 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR] | 20000164625 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR] | 20000164840 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR] | 20000164161 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR] | 20000164087 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR] | 20000164187 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR] | 20000164197 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR] | 20000164667 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR] | 20000164263 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR] | 20000164913 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR] | 20000164521 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR] | 20000164541 |
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR] | 20000164762 |
<tbody>
</tbody>
Code:
Option Explicit
Sub Find_20000163989()
'Requires a reference to the Microsoft Scripting Runtime.
'Tools | References -> Microsoft Scripting Runtime
Dim fso As FileSystemObject: Set fso = New FileSystemObject
Dim str_To_Find, strNextLine As String
Dim txtStream, LastRow
Dim IdNumber, rngCell As Range
'Assume your id numbers in Column A. Find last empty row.
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'Assign Variable IdNumber to Range
Set IdNumber = Sheets("Sheet1").Range("A2:A" & LastRow - 1)
'Cycle through every cell of the Range -> IdNumber
For Each rngCell In IdNumber
'Open your file with the one million lines ! ! !
Set txtStream = fso.OpenTextFile("c:\temp\textfile.txt", ForReading, False)
'Assign the value of the cell to variable str_To_Find
str_To_Find = rngCell.Value
'Loop through the lines to find the Identification Number
'And print the line to Immediate window
Do While Not txtStream.AtEndOfStream
strNextLine = txtStream.ReadLine
If InStr(1, strNextLine, str_To_Find) > 0 Then
Debug.Print strNextLine
'If Identification Number found exit the Do Loop.
Exit Do
End If
Loop
'Close the stream
txtStream.Close
Next
End Sub