I have a macro in which I am trying to sort data. The data is stored in a
table and every column has a unique header. One of the columns have a header
called €œSec type€�. If the vale in that column is €œGOV BOND€� then I want to
look at the column marked by the header €œSec ID€�. The data in that column is
stored like this: NAME YIELD MATURITY. For example: ACGB 4.75 0311, T 5.10
0420, FROG 3.11 1020X. Now as you can see the €œSec ID€� has the same form but
it differs in terms of characters etc. I want to sort the entire table based
on maturity. Thus, I want to single out the maturity and then sort the list.
For certain reasons I do not want to add new columns etc. but handle this
€œinside€� the program.

The way I attack the problem now is that I use text-to-columns and tries to
store the new columns in the matrix. I have spare room for the new data so
that is not a problem. My problem is that I do not know how to refer to the
places in the matrix where I want to store the data. Let me show you some
code:

I loop through the table by looking in the €œSec ID€�-column. For every row I
look to see if the text in €œSec type€� is €œGOV BOND€�. If so I increase the
matrix and then I use text-to-columns (from a recorded macro).

Do Until IsEmpty(rngSecID.Offset(r, lngTypeColumn))
r = r + 1
If rngSecID.Offset(r, lngTypeColumn).Text = strGovBond Then
ReDim varDataMatris(1 To tableLength, 1 To r)
rngSecID.Offset(r, 0).Select
Selection.TextToColumns Destination:=varDataMatris(tableLength - 2,
tableLength - 1, tableLength), DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False,
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)),
TrailingMinusNumbers:=True
End If
Loop

My problem is that I do not know how to refer to the proper destination. Now
I have: Destination:=varDataMatris(tableLength - 2, tableLength - 1,
tableLength) which is completely wrong. I want to place the data retrieved
from the text-to-columns in the rightmost columns in the matrix. The row is
to be the same as in the list i.e. r. I really have no idea how to write
that. Please help me out! Any help is very much appreciated but I cannot
change the setup very much but I need to find the code for how to specify the
right address in matrix! Many thanks in advance!