Having trouble with code for lookup function

bronz01

New Member
Joined
May 19, 2013
Messages
23
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?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
May 19, 2013
Messages
23
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

New Member
Joined
May 19, 2013
Messages
23
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
May 19, 2013
Messages
23
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top