Having trouble with code for lookup function

[bronz01]

Hi everyone, I'm trying to write a function that searches a range for a particular value and returns the value of a cell that is offset from the matching cell by the values specified in the other arguments. My code is:

Function LOOKUPDATA(LookupVal As Date, OffsetRow As Integer, OffsetCol As Integer) As Integer

Dim Cell As Range

For Each Cell In Worksheets("Data by month").Range("A1:A100")
If Cell.Value = LookupVal Then
LOOKUPDATA = Cell.Offset(OffsetRow, OffsetCol).Value
End If
Next Cell

End Function

This runs perfectly but always returns a value of zero, no matter what values I give the arguments. I've tried using a message box to see the value of each cell as it steps through the range, and even when the value in the message box is the same as that in the first argument it just keeps on going.

What am I doing wrong?

 

[JoeMo]

Assuming the lookup data point is an integer:
Function LOOKUPDATA(LookupVal As Date, OffsetRow As Integer, OffsetCol As Integer) As Integer

Dim Cell As Range

For Each Cell In Worksheets("Data by month").Range("A1:A100")
If Cell.Value = LookupVal Then
LOOKUPDATA = Cell.Offset(OffsetRow, OffsetCol).Value
Exit For
End If
Next Cell

End Function

 

[bronz01]

Hmm, thanks but still no dice. It seems that LOOKUPDATA just isn't being assigned a value despite being told where to get that value from. If it's any help the cell being referenced in LookupVal is part of a merged range, as is the cell it should find, but it's the leftmost one in both cases and shows up as having the correct value when clicked on (and when the message box displayed it's value).

 

[bronz01]

Ok, with further checking it seems to now recognise where to stop, but resolutely refuses to get a value from the cell it's been told to offset to. I've traced the whole process with message boxes through each iteration of the loop and it stops at the right place then displays nothing in the message box for the value of that cell. I can see on the sheet that the cell has a value (64).

 

[JoeMo]

Ok, with further checking it seems to now recognise where to stop, but resolutely refuses to get a value from the cell it's been told to offset to. I've traced the whole process with message boxes through each iteration of the loop and it stops at the right place then displays nothing in the message box for the value of that cell. I can see on the sheet that the cell has a value (64).

Have you tried unmerging the cells being referenced and/or making the function a variant rather than integer?

 

[bronz01]

Unmerging the cells worked! Actually, unmerging the cells in the range that was being looked up, rather than the vaue to be looked up, was what did the job. Any idea why this should be? In both cases if you click on the leftmost cell of the merged range it displayed the value I was after.