MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to import text file and use VBA to perform INDEX & MATCH


Posted by Andrew Cheung on June 11, 2001 6:10 AM

I have a text file having the following formats:
02105010 -104545.00 -452383.311
02105995 -104545.00 -452383.315
03115136 -99666.43 -398521.181
99115137 -46021.34 -264115.371
04115138 -7606.47 -30346.971
08115139 -2120584.89 -1796277.431
10115140 447390.08 1763911.081
12115144 -4200.00 -16755.901
31115138 -606.47 -303.971
03115144 14200.00 6755.901

In Excel, I have the following macro to read the text file:
Sub GL6113()

Dim inhandle As Integer
Dim readdata
Dim ccy
Dim gslcode
Dim fcy
Dim dirc
Dim counter As Integer
inhandle = FreeFile()
counter = 2

Sheets("working").Select
Sheets("Working").Range("c2:d65000").ClearContents
Sheets("Working").Range("A2:d65000").ClearContents
dirc = "C:\ES\GL6113.txt"
Open dirc For Input As inhandle
Do While Not EOF(inhandle)
Line Input #inhandle, readdata

ccy = Mid(readdata, 1, 3)
gslcode = Mid(readdata, 4, 6)
fcy = Mid(readdata, 11, 16)

If ccy <> "" Then
Sheets("Working").Range("a" & counter) = ccy
Sheets("Working").Range("b" & counter) = gslcode
Sheets("Working").Range("c" & counter) = fcy
End If
Loop
Close inhandle

By these macro. it will read the ccy in Cell(A1), placing gslcode in Cell(B1), and the amount in Cell (C1).
How could I use macro to do the following?
I want to place the 'Amount' accordign t othe 'ccy', say '01' in column B, '02' in column D. '03' in column F and so on, and place the amount according to the gslcode by row (but gslcode is not in any order, which is similar to the function "INDEX & MATCH".
How could I do this?
Thank You


Posted by Damon Ostrander on June 11, 2001 4:48 PM

Hi Andrew,

Just use the Cells property, which allows you to refer to a range (cell) by row and column indices:

Sheets("Working").Cells(gslcode,ccy+1) = fcy

Damon