VBA Loop troubles

[hfler]

Public Function bidsearch(monthyear As String, k As Double) As Double
Dim searchrange As Range
Dim dummy As Range
Set searchrange = Range("bbg_strike", Range("bbg_strike").End(xlDown))
For Each dummy In searchrange
    If dummy.Value = k And dummy.Offset(-1, 0) = monthyear Then
    bidsearch = dummy.Value.Offset(2)
    Else
    bidsearch = "error"
    End If
Next dummy
 
End Function

Does anyone have any idea why this return "#VALUE!"?


Thanks!

 

[Airfix9]

Just a quick stab, the following appears incorrect:

Range("bbg_strike", Range("bbg_strike").End(xlDown))

I suggest:

Range(range("bbg_strike"), Range("bbg_strike").End(xlDown))

 

[hfler]

Thanks Airfix, I changed that, still receiving the VALUE! message though.

 

[Airfix9]

Can you post some example data and indicate the Range "bbg_strike", please?

 

[hfler]

Sure. bbg strike range is below,
<TABLE style="WIDTH: 59pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=79><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 59pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=20 width=79>

​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>

455​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>

460​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>

465​

</TD></TR></TBODY></TABLE>
dummy.offset(1,0) is,

<TABLE style="WIDTH: 59pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=79><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>

4/1/11​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>

5/1/11​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>

6/1/11​

</TD></TR></TBODY></TABLE>​

dummy.value.offset(2) is,

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64 align=right>0.125</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0.125</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right>0.135</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 align=right></TD></TR></TBODY></TABLE>

 

[Airfix9]

OK, looking at your code, you state:

If dummy.Value = k And dummy.Offset(-1, 0) = monthyear Then

Yet, in your last reply, you state:

dummy.offset(1,0) is,

4/1/11
5/1/11
6/1/11

Is it 1 or -1?

 

[hfler]

**that's my mistake, the code is correct - it is dummy.offset(-1,0)

 

[Airfix9]

So it is looking to see if you can match all three in a row with your selection? You are declaring monthdate as a string, are your dates formatted as text or as numbers?

 

[hfler]

Oh, good point. The format for monthyear "MMM-YY", but they're entered as 1/1/2000. Let me try changing that.

What I want to do is search the row until "k" is found in the bbg_strike row, and then search dummy.offset(-1, 0) until that value equals "monthyear", and then return that value for dummy.offset(2,0).

Thanks very much for your help, I really appreciate it.



*****Changing the variable type for monthyear didn't work.

 

[Airfix9]

Just so I understand...

You search range bbg_strike until you find the number you are looking for (k). Let us say you are looking for 460 (the second entry in the test data).

Then, you search from that column until you find the date that you are looking for - let us say it is 6/1/2011 - and return the value 2 below it. In this case 0.135.

Some questions:

What if you don't find k, if it isn't in the list? Does that return "error"?
What if you don't find the date after you have found k? Does that return "error"?

Finally, how have you declared monthyear now?