Macro Fails in Worksheet

[Adrian]

Below is a macro function that works fine in the immediate
window, but gives #VALUE when I try to use it in a
worksheet.
I had this problem recently with an earlier version of
this macro and was advised to use Match instead of Find,
since apparently Find is known to fail in this fashion on
earlier versions of Excel.
This macro needs to work in Excel 97 onwards.

Function Sinfo(lookupkey, lookupval, rtnkey)
Set rng = Workbooks("data.xls").Worksheets
("students").Range("1:1")
lkcol = Application.WorksheetFunction.Match(lookupkey,
rng, 0)
rtncol = Application.WorksheetFunction.Match(rtnkey, rng,
0)
Set rng2 = Workbooks("data.xls").Worksheets
("students").Range(Cells(1, lkcol), Cells(65535, lkcol))
rtnrow = Application.WorksheetFunction.Match(lookupval,
rng2, 0)
Sinfo = Workbooks("data.xls").Worksheets("students").Cells
(rtnrow, rtncol)
End Function

 

[Tom Ogilvy]

Function Sinfo(lookupkey, lookupval, rtnkey)
On Error goto ErrHandler
Set rng = Workbooks("data.xls").Worksheets _
("students").Range("1:1")
lkcol = Application.WorksheetFunction.Match(lookupkey,rng, 0)
rtncol = Application.WorksheetFunction.Match(rtnkey, rng,0)
With Workbooks("data.xls").Worksheets("students")
Set rng2 = .Range(.Cells(1, lkcol), .Cells(65535, lkcol))
End With
rtnrow = Application.WorksheetFunction.Match(lookupval, rng2, 0)
Sinfo = Workbooks("data.xls").Worksheets("students").Cells(rtnrow, rtncol)
Exit Function
ErrHandler:
SInfo = "Match not made"
End Function

 

[JE McGimpsey]

Your function works for me as long as workbook "Data.xls" is open,
there's a sheet "students" in it, and the lookupval can be found in the
column indicated by lookupkey.

This variant will give a #Ref! error if workbook "data.xls" is not open,
and an #N/A error if the lookupval is not found in the lookupkey column:

Public Function Sinfo(lookupkey, lookupval, rtnkey) As Variant
Dim rng As Range
Dim lkcol As Long
Dim rtncol As Long
On Error GoTo No_Data
Set rng = Workbooks("data.xls").Worksheets("students").Cells
On Error GoTo No_Match
With rng
lkcol = Application.Match(lookupkey, .Rows(1), 0)
rtncol = Application.Match(rtnkey, .Rows(1), 0)
Sinfo = Application.Index(.Columns(rtncol), _
Application.Match(lookupval, .Columns(lkcol), 0))
End With
Exit_Sub:
On Error GoTo 0
Exit Function
No_Data:
Sinfo = CVErr(xlErrRef)
Resume Exit_Sub
No_Match:
Sinfo = CVErr(xlErrNA)
Resume Exit_Sub
End Function

 

[JE McGimpsey]

You could do this more efficiently using using worksheet functions:

To make things easier, define a couple of names in your workbook
(Insert/Name/Define):

Name in workbook: dataA
Refers to: =INDIRECT("[data.xls]students!A:A")

Name in workbook: data1
Refers to: =INDIRECT("[data.xls]students!1:1")

Then, you could use the worksheet function:

=INDEX(OFFSET(dataA,,MATCH(rtnkey,data1,0)-1), MATCH(lookupval,
OFFSET(dataA,,MATCH(lookupkey,data1,0)-1),0))

 

[Adrian]

Tom, thanks for this.
Can you spare a few moments to explain why your version
works and mine doesn't? What's so magical about "With".

 

[JE McGimpsey]

Cells() defaults to the activesheet unless qualified

Worksheets("students").Range(Cells(1, 1), Cells(100, 1))

is equivalent to


Worksheets("students").Range(ActiveSheet.Cells(1, 1),
Activesheet.Cells(100, 1))

If "students" isn't the activesheet, then the Range method will fail.

 

[Adrian]

Many thanks.

As it happens no sheet in the workbook "data.xls" will
ever be the active sheet!

And thank you for your alternative suggestion re worksheet
functions. Interesting, but not appropriate in the grand
scheme of things. But I shall keep it in storage for the
future.