I am using2003. Now I need to extract certain information from text files. My text file is like below:
0.208485 1
0.213333 1
0.218182 0.9
0.22303 0.6
0.227879 0.42
0.232727 0.3
The file contains 2 columns, first I need to check the second column and find to value closest to 0.5 and read the value in the same row in the first column to(in above case, the value is 0.227879). Could you give me some good ideas of this question?
And I do not want to read whole text file tobecause the file is big. So it would be better we can first do the data filtering in text file and then just read the information we want to excel.
I like this question because it exposes a capability of VBA inthat few people know about, so it is rarely taken advantage of.
Here is some code that does what you describe:
________________________________________________________________________________
Sub FindClosest()
'Finds value closest to 0.5 in column 2 of Find.txt file and place 'value from column 1 in cell B4 of Sheet1 Dim Val1 As Double 'Value from column 1 Dim Val2 As Double 'Value from column 2 Dim ClosestVal1 As Double Dim ClosestVal2 As Double Dim Rec As String Dim Vals As Variant ClosestVal2 = 1000000# Open "Find.txt" For Input As #1 'loop thru all records in file
Do
Line Input #1, Rec Vals = Split(Rec, " ") Val1 = Vals(0) Val2 = Vals(1) If Abs(Val2 - 0.5) < Abs(ClosestVal2 - 0.5) Then ClosestVal1 = Val1 ClosestVal2 = Val2 End If Loop Until EOF(1) Worksheets("Sheet1").Range("B4") = ClosestVal1 MsgBox "Done", vbInformation
End Sub
____________________________________________________________________________________
In this code I assume that the text file is named "Find.txt" and is located in the current working folder. If it is not in the working folder you can use the full path, e.g., C:\My Documents\VBAstuff\Find.txt".
When if finds the second value closest to 0.5 it places the first value in cell B4 of Sheet1.
Advertisement