AlwaysStuck
09-15-2011, 06:05 AM
Hi, forgive me if this is typed out badly. I'm new to this site. I'm trying to write a VBA code in which I look up an array for a date and for a string. I then went to get the value 5 columns next to the date.
So for example say I have set variable "DateIwant" to 03/06/2005 and I have set a variable "ISINcode" to CC993, I want the code to be able to locate that the second row is what I want. And then I want to get the value from F2 (ie .004). I need to do this using variables so I can't just search #03/06/2005# and ""CC993"" explicitly. I'm use variables to cycle through a line of code.
I've tried something along the lines of
prev_ytm = Evaluate("INDEX($F$1:$F$3,MATCH(DateIwant&ISINcode,$A$1:$A$3&$B$1:$B$3,0))")
The code was from internet searches. I ran it but I get a runtime error 13. I think the date is possibly stuffing me up.
I don't know if an offset thing will help. Was wondering if anyone can help me. Sorry for the bad formatting below. It's supposed to be 6 columns (A, B, C, D, E, F)
A B C D E F
05/09/2010 CC005 1 1 1 .005
03/06/2005 CC993 1 1 1 .004
08/04/2015 CC352 1 1 1 .009
So for example say I have set variable "DateIwant" to 03/06/2005 and I have set a variable "ISINcode" to CC993, I want the code to be able to locate that the second row is what I want. And then I want to get the value from F2 (ie .004). I need to do this using variables so I can't just search #03/06/2005# and ""CC993"" explicitly. I'm use variables to cycle through a line of code.
I've tried something along the lines of
prev_ytm = Evaluate("INDEX($F$1:$F$3,MATCH(DateIwant&ISINcode,$A$1:$A$3&$B$1:$B$3,0))")
The code was from internet searches. I ran it but I get a runtime error 13. I think the date is possibly stuffing me up.
I don't know if an offset thing will help. Was wondering if anyone can help me. Sorry for the bad formatting below. It's supposed to be 6 columns (A, B, C, D, E, F)
A B C D E F
05/09/2010 CC005 1 1 1 .005
03/06/2005 CC993 1 1 1 .004
08/04/2015 CC352 1 1 1 .009