F
freddie mac
I have a macro in which I am trying to sort data. The data is stored i
a table and every column has a unique header. One of the columns have
header called “Sec type”. If the vale in that column is “GOV BOND” the
I want to look at the column marked by the header “Sec ID”. The data i
that column is stored like this: NAME YIELD MATURITY. For example: ACG
4.75 0311, T 5.10 0420, FROG 3.11 1020X. Now as you can see the “Se
ID” has the same form but it differs in terms of characters etc. I wan
to sort the entire table based on maturity. Thus, I want to single ou
the maturity and then sort the list. For certain reasons I do not wan
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 an
tries to store the new columns in the matrix. I have spare room for th
new data so that is not a problem. My problem is that I do not know ho
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 ever
row I look to see if the text in “Sec type” is “GOV BOND”. If so
increase the matrix and then I use text-to-columns (from a recorde
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(tableLengt
- 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 prope
destination. Now I have: Destination:=varDataMatris(tableLength - 2
tableLength - 1, tableLength) which is completely wrong. I want t
place the data retrieved from the text-to-columns in the rightmos
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 i
very much appreciated but I cannot change the setup very much but I nee
to find the code for how to specify the right address in matrix! Man
thanks in advance
a table and every column has a unique header. One of the columns have
header called “Sec type”. If the vale in that column is “GOV BOND” the
I want to look at the column marked by the header “Sec ID”. The data i
that column is stored like this: NAME YIELD MATURITY. For example: ACG
4.75 0311, T 5.10 0420, FROG 3.11 1020X. Now as you can see the “Se
ID” has the same form but it differs in terms of characters etc. I wan
to sort the entire table based on maturity. Thus, I want to single ou
the maturity and then sort the list. For certain reasons I do not wan
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 an
tries to store the new columns in the matrix. I have spare room for th
new data so that is not a problem. My problem is that I do not know ho
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 ever
row I look to see if the text in “Sec type” is “GOV BOND”. If so
increase the matrix and then I use text-to-columns (from a recorde
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(tableLengt
- 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 prope
destination. Now I have: Destination:=varDataMatris(tableLength - 2
tableLength - 1, tableLength) which is completely wrong. I want t
place the data retrieved from the text-to-columns in the rightmos
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 i
very much appreciated but I cannot change the setup very much but I nee
to find the code for how to specify the right address in matrix! Man
thanks in advance