有许多表,它的数据不规范,)见附后中宿舍名单,宿舍费用表)也就是无法通过函数查找与引用,想设计一个自定义函数
原理是,  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
以上代码有问题,我一时也搞不懂出来那里
请大家帮忙啊,偶系菜鸟来的

解决方案 »

  1.   

    VLookup 不是这样用的VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value    为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array    为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用,例如数据库或列表。如果 range_lookup 为 TRUE,则 table_array 的第一列中的数值必须按升序排列:…、-2、-1、0、1、2、…、-Z、FALSE、TRUE;否则,函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE,table_array 不必进行排序。通过在“数据”菜单中的“排序”中选择“升序”,可将数值按升序排列。Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。Col_index_num    为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时,返回 table_array 第一列中的数值;col_index_num 为 2,返回 table_array 第二列中的数值,以此类推。如果 col_index_num 小于 1,函数 VLOOKUP 返回错误值值 #VALUE!;如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值 #REF!。Range_lookup    为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE,函数 VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。说明如果函数 VLOOKUP 找不到 lookup_value,且 range_lookup 为 TRUE,则使用小于等于 lookup_value 的最大值。 
    如果 lookup_value 小于 table_array 第一列中的最小数值,函数 VLOOKUP 返回错误值 #N/A。 
    如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE,函数 VLOOKUP 返回错误值 #N/A。 
    N = Application.WorksheetFunction.VLookup(Str, arr, 1, 0)
    用法不对,你这句的意思是:在arr的第一列中查找str,找到后返回第一列的值,那不还是str吗?
    你可以参考Match函数来找匹配的行。
      

  2.   

    序号 房号 姓名 姓名 姓名 姓名 姓名 姓名 姓名 姓名 实住人数 标准配置 男/女
    1 A201 张光印 宋鹏飞 李梦辉 刘玉川 刘洋 陈常胜 刘海领 7 8 男
    2 A202 刘运华 刘华山 聂红星 3 6 男
    3 A203 厨房 6 男
    4 A204 江东亮 刘志通 赵军超 高浩博 易阳 张松 6 6 男
    5 A205 杨庆才 伍庆佳 余振和 沈杰 4 6 男
    6 A206 冯映红 任永辉 刘晓梁 3 6 男
    7 A207 杨兵 易厚东 郭红 唐林山 4 6 男
    8 A210 唐红 陶敏 苟军 赵朋 周仲举 谢正祥 6 6 男
    9 A212 肖树辉 周第福 2 6 男
    数据不规范,没法引用
      

  3.   

    我的意思是通过姓名查找,所在宿舍
    N = Application.WorksheetFunction.VLookup(Str, arr, 1, 0)不是查找STR在这个数组的值嘛.
    我是想通过这个值来确定其所在行
    提高手再详细点
      

  4.   

    序号 房号 姓名 姓名 姓名 姓名 姓名 姓名 姓名 姓名 实住人数 标准配置 男/女
    1 A201 张光印 宋鹏飞 李梦辉 刘玉川 刘洋 陈常胜 刘海领 7 8 男
    2 A202 刘运华 刘华山 聂红星 3 6 男
    3 A203 厨房 6 男
    4 A204 江东亮 刘志通 赵军超 高浩博 易阳 张松 6 6 男
    5 A205 杨庆才 伍庆佳 余振和 沈杰 4 6 男
    6 A206 冯映红 任永辉 刘晓梁 3 6 男
    7 A207 杨兵 易厚东 郭红 唐林山 4 6 男
    8 A210 唐红 陶敏 苟军 赵朋 周仲举 谢正祥 6 6 男
    9 A212 肖树辉 周第福 2 6 男
    10 A219 巫福东 巫丰平 2名 班长 2 4 男
    数据是这样,怎么能过姓名引用宿舍号
      

  5.   


    Dim d()
    Private Sub Init_Data()
        Dim n As Integer, i As Integer, j As Integer
        n = [A65530].End(xlUp).Row
        ReDim d(n)
        For i = 1 To n
            For j = 3 To Cells(i, 20).End(xlToLeft).Column
                d(i) = d(i) & "|" & Cells(i, j) & "|"
            Next j
        Next i
    End SubPrivate Function WLOOKUP(strName As String) As String
        Dim i As Integer
        For i = 1 To UBound(d)
            If InStr(1, d(i), "|" & Trim(strName) & "|") > 0 Then
                WLOOKUP = Cells(i, 2)
                Exit Function
            End If
        Next i
        WLOOKUP = ""
    End FunctionSub main()
        Dim strName As String
        strName = "余振和"
        Debug.Print strName & "的宿舍号:" & WLOOKUP(strName)
    End Sub结果:
    余振和的宿舍号:A205代码你自己完善吧。
    首先执行一遍Init_Data,将所有的人员放入数组在main中执行查找,WLOOKUP不为空则找到,为空则未找到。
      

  6.   

    将你的数据放在sheet1
    在sheet2中做VBA:Private Sub Worksheet_Activate()
        Dim i As Integer, j As Integer, k As Integer
        With Sheet1
            k = 1
            For i = 2 To .[A65530].End(xlUp).Row
                For j = 3 To .Cells(i, 20).End(xlToLeft).Column
                    If IsNumeric(.Cells(i, j)) Then Exit For
                    Cells(k, 1) = .Cells(i, j)
                    Cells(k, 2) = .Cells(i, 2)
                    k = k + 1
                Next j
            Next i
        End With
    End Sub
    这样将在sheet2中产生姓名和宿舍的对照表,有了这表,你想干什么都成了。
      

  7.   

    sheet2中的结果:伍庆佳 A205
    余振和 A205
    沈杰 A205
    冯映红 A206
    任永辉 A206
    刘晓梁 A206
    杨兵 A207
    易厚东 A207
    郭红 A207
    唐林山 A207
    唐红 A210
    陶敏 A210
    苟军 A210
    赵朋 A210
    周仲举 A210
    谢正祥 A210
    肖树辉 A212
    周第福 A212
    巫福东 A219
    巫丰平 A219
    2名 A219
    班长 A219你就可以编程查找宿舍了,自己发挥吧。
      

  8.   

    不是很清楚用法,不过以前在过程中也好像是同样的格式?
    比如a= Worksheets("财务").Range("G5")可用   Range("G"&i)=Range("A4")可用
    a=Worksheets("财务").Range("G"&i) (已定义Dim i As Integer)不可用
    i为一个从1到200的循环 i=i+1 通过循环给一个变量赋值,不可用
    是不是要把前面那个变量改为动态数组??
    请帮忙解释一下,多谢
      

  9.   

    这是我刚开始时写的代码,后来就把自己整迷糊了
    Function WLOOKUP(Str As String, a As Integer, b As Integer, column As String, sheet As String) As String
        Dim GetPY As String, N As Integer
        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.CountIf(arr, Str)
          If N > 0 Then
          GetPY = Application.WorksheetFunction.INDIRECT(column & i)
          i = i + 1
     end if 
       Next i
      
      HYPY = GetPY
    End Function
    这里的数组赋值是不是错误了?  
    Range(Cells(i, a), Cells(i, b))能不能这样用?
    汗颜啊,求解
      

  10.   

    Range(Cells(i, a), Cells(i, b))没错,可以这样用啊,标示i行的a-b列。应该是Sheets(sheet)错了吧,sheets("表名称")才对。
      

  11.   

    应该是Sheets(sheet)错了吧,sheets("表名称")才对。
    哦谢谢,我的意思sheet是一个变量名,就是使用者输入的那个表格名
    按道理是可以这样用,但老是不能通过,要么就是没有结果
    郁闷
      

  12.   

    本来以为是Application.WorksheetFunction.INDIRECT(column & i)
    这一句i变量类型出错,可以加上""也没有
    我已经用EXCEL系统函数解决了
    办法如下:1用INDIRECT("Sheet2!"&ADDRESS(A$1+1,ROW($A2)))把横排变竖排,把宿舍名放最后
              2能过多重IF
    IF(COUNTIF(函数自动转换!2:2,A1)>0,HLOOKUP(A1,函数自动转换!A2:J10,9,0),IF(COUNTIF(函数自动转换!3:3,A1)>0,HLOOKUP(A1,函数自动转换!A3:J10,8,0),IF(COUNTIF(函数自动转换!4:4,A1)>0,HLOOKUP(A1,函数自动转换!A4:J10,7,0),IF(COUNTIF(函数自动转换!5:5,A1)>0,HLOOKUP(A1,函数自动转换!A5:J10,6,0),IF(COUNTIF(函数自动转换!6:6,A1)>0,HLOOKUP(A1,函数自动转换!A6:J10,5,0),IF(COUNTIF(函数自动转换!7:7,A1)>0,HLOOKUP(A1,函数自动转换!A7:J10,4,0),IF(COUNTIF(函数自动转换!8:8,A1)>0,HLOOKUP(A1,函数自动转换!A8:J10,3,0),HLOOKUP(A1,函数自动转换!A9:J10,2,0))))))))直接引用
    可还是想编一个自定义函数,望指点




      

  13.   

    这个自定义函数最接近,在过程中正确但函数中结果老是变来变去,虽然引用的还是那一列,什么原因?
    比如查一个,张光印,但结果有时正确,有时不是那个宿舍号
    Function Wlookup(ins As String, a As Integer) As String
        Dim out As String
        Dim b As Integer
        Sheets("sheet1").Select
        Sheets("宿舍名单").Select
        Cells.Find(What:=ins, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , MatchByte:=False, SearchFormat:=False).Activate
         b = ActiveCell.Row  '返回鼠标所在行d = ActiveCell.Column 返回鼠标所在列
        out = Worksheets("宿舍名单").Cells(b, a)
        Sheets("sheet1").Select
        Wlookup = out
    End Function
    能帮帮忙嘛,多谢了
      

  14.   


    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 = Nothing
    End Function
    用法举例:
    单元格=WLOOKUP("余振和",Sheet1!A:M)
    结果:
    单元格=A205