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!

 

[hfler]

My mistke. I should include that I'm a new VBA user - I mixed up by column and row offset commands. In response to your questions, I have manthyear, and I know k and monthyear will match, because I entered in some test values to make sure they do.

Should look like this,

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

 

[Airfix9]

So, is it solved?

Don't worry about being new to VBA, we all were at some point.

 

[hfler]

No, still getting that VALUE! returned. Here's my new code in full now,

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

 

[Airfix9]

Try this:

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

Part of the problem is that you have declared your Function as a double. You can't have "error" as a response as that is a string. Changed the Function to a Variant.

 

[hfler]

Ah. That worked to return "error". But there should still be an actual numerical value returned. I think I need to add on more parameter to check in my if statement, because there might be duplicate entries that meet the parameters, as of now. Thanks again.