:不规范数据的查找引用

求助:不规范数据的查找引用
有许多表,它的数据不规范,)见附后中宿舍名单,宿舍费用表)也就是无法通过函数查找与引用,想设计一个自定义函数
原理是, 1通过逐行查找,找到这个值所在单元格
  2通过检索这个单元格行号,引用其所在行的某一列数据
遇到的问题是:
Function WLOOKUP(Str As String, a As String, b As String, column As String, sheet As String) As String
  Dim GetPY As String, N As String
  Str = Application.InputBox(Prompt:="输入条件", Type:=1)
  a = Application.InputBox(Prompt:="输入数据开始列", Type:=1)
  b = Application.InputBox(Prompt:="输入数据终止列", Type:=1)
  column = Application.InputBox(Prompt:="输入引用列", Type:=1)
  sheet = Application.InputBox(Prompt:="输入引用表", Type:=1)
  On Error Resume Next
  For i = 1 To 1000
  Dim arr
  Set arr = Sheets(sheet).Range(Cells(i, a), Cells(i, b))
  N = Application.WorksheetFunction.VLookup(Str, arr, 1, 0)
  If N > 0 Then
  GetPY = Application.WorksheetFunction.INDIRECT(column & i)
  i = i + 1
  Next i
  
  HYPY = GetPY
End Function
以上代码有问题,我一时也搞不懂出来那里
请大家帮忙啊,偶系菜鸟来的

[解决办法]

VB code

Public Function Wlookup(sName As String, myArray As Range) As String    If Len(sName) = 0 Then Wlookup = "": Exit Function    Set r = myArray.Find(sName)    If r Is Nothing Then        Wlookup = "Not Found"    Else        Wlookup = Worksheets(myArray.Worksheet.Name).Cells(r.Row, 2)    End If    Set r = NothingEnd Function