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)
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
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!