PDA

View Full Version : Searching an array using two criterias



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

xld
09-15-2011, 06:26 AM
prev_ytm = Evaluate("INDEX($F$1:$F$3,MATCH(""" & CLng(DateIWant) & ISINcode & """,$A$1:$A$3&$B$1:$B$3,0))")

AlwaysStuck
09-15-2011, 06:38 AM
OMG. THANK YOU! I can't believe I spent 4 hours trying to do that on my own. Thanks so much, it works!

Oh, what are the 2 """s inside MATCH() for?

xld
09-15-2011, 07:33 AM
to makeit a string being looked up.

AlwaysStuck
09-15-2011, 08:00 AM
EDIT: nvm, just deleted the problem I had. I found out that I didn't change the $HL$10 to $HL$21466.

New question: Does excel normally crash when you run code on 20,000+ observations? I'm running the code at the moment and it's stuck on (Not responding). I'm not sure whether to leave it there in case it might actually still be running the code in the background.

xld
09-15-2011, 08:58 AM
If you are doing 20,0000 evaluates, expect to wait a while.