在Excel中,VLOOKUP()是最常被使用的查引用函数,但这个函数有一个问题,就是被查的对象必须位于所查区域的第一列位置上,引用是使用相对地址进行操作的,而且,当被查区域不存在被查对象时,函数返回值是不正确的。
下面第一个函数 S_FIND在以前介绍过,该函数正是针对这一问题,即所查对象可以位于被查区域的任意列上,查的应用也使用绝对地址,即直接通过列标来引用。
但我最初设计的时候,该函数仅能完成对单元格的精确查,无法完成模糊查和继续查。
重新设计的查函数包括以下组合:S_FIND(M_code, M_SHEET, M_AREA, M_COL As String)实现对被查对象的精确查、S_FINDP(M_code, M_SHEET, M_AREA, M_COL As String)实现对被查对象的模糊查、S_FINDN(M_code, M_SHEET, M_AREA, M_COL As String)实现对被查对象的继续查。其中,实现继续查需引入全局变量,即在模块首部进行变量定义,而且S_FINDN函数的使用必须在S_FIND或S_FINDP被使用后才起作用。同时,当第四个函数变量M_COL被以空字符(即"")代入时,该三函数返回值为被查对象的行标。
Dim M_CBUT, M_CROW As Integer
Function S_FIND(M_code, M_SHEET, M_AREA, M_COL As String)
'在 M_SHEET 工作表 M_AREA 范围中 精确查 M_CODE 所在行,并返回其对应的 M_COL 列单元格的值。
'以上函数参数均为文本或其值为文本的单元格或表达式。
Dim M_ROW As Integer
Dim M_STEP As Integer
M_CBUT = 0
M_CROW = 0
M_RANGE = ""
On Error GoTo 100
M_code = Trim(M_code)
If M_SHEET = "" Then
'在 M_SHEET 工作表 M_AREA 范围中 精确查 M_CODE 所在行,并返回其对应的 M_COL 列单元格的值。
'以上函数参数均为文本或其值为文本的单元格或表达式。
Dim M_ROW As Integer
Dim M_STEP As Integer
M_CBUT = 0
M_CROW = 0
M_RANGE = ""
On Error GoTo 100
M_code = Trim(M_code)
If M_SHEET = "" Then
M_ROW = Range(M_AREA).Find(Trim(M_code), LOOKAT:=xlWhole).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_COL & M_ROW)
End If
Else
M_ROW = Range(M_SHEET & "!" & M_AREA).Find(Trim(M_code), LOOKAT:=xlWhole).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_SHEET & "!" & M_COL & M_ROW)
End If
End If
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_COL & M_ROW)
End If
Else
M_ROW = Range(M_SHEET & "!" & M_AREA).Find(Trim(M_code), LOOKAT:=xlWhole).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_SHEET & "!" & M_COL & M_ROW)
End If
End If
M_CBUT = 1
M_CROW = M_ROW
100:
S_FIND = M_RANGE
End Function
M_CROW = M_ROW
100:
S_FIND = M_RANGE
End Function
Function S_FINDP(M_code, M_SHEET, M_AREA, M_COL As String)
'在 M_SHEET 工作表 M_AREA 范围中 模糊查 M_CODE 所在行,并返回其对应的 M_COL 列单元格的值。
'以上函数参数均为文本或其值为文本的单元格或表达式。
Dim M_ROW As Integer
Dim M_STEP As Integer
M_CBUT = 0
M_CROW = 0column函数和vlookup函数
M_RANGE = ""
On Error GoTo 100
M_code = Trim(M_code)
If M_SHEET = "" Then
M_ROW = Range(M_AREA).Find(Trim(M_code), LOOKAT:=xlPart).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_COL & M_ROW)
End If
Else
M_ROW = Range(M_SHEET & "!" & M_AREA).Find(Trim(M_code), LOOKAT:=xlPart).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_code = Trim(M_code)
If M_SHEET = "" Then
M_ROW = Range(M_AREA).Find(Trim(M_code), LOOKAT:=xlPart).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_COL & M_ROW)
End If
Else
M_ROW = Range(M_SHEET & "!" & M_AREA).Find(Trim(M_code), LOOKAT:=xlPart).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_SHEET & "!" & M_COL & M_ROW)
End If
End If
M_CBUT = 1
M_CROW = M_ROW
100:
S_FINDP = M_RANGE
End Function
End If
End If
M_CBUT = 1
M_CROW = M_ROW
100:
S_FINDP = M_RANGE
End Function
Function S_FINDN(M_code, M_SHEET, M_AREA, M_COL As String)
'在 M_SHEET 工作表 M_AREA 范围中 再次查 M_CODE 所在行,并返回其对应的 M_COL 列单元格的值。
'以上函数参数均为文本或其值为文本的单元格或表达式。
'此函数必须在S_FIND或S_FINDP被点击后使用
Dim M_ROW As Integer
Dim M_STEP As Integer
M_RANGE = ""
On Error GoTo 100
If M_CBUT = 0 Then GoTo 100
M_code = Trim(M_code)
If M_SHEET = "" Then
M_ROW = Range(M_AREA).FindNext(AFTER:=Cells(M_CROW, Range(M_AREA).Column)).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_COL & M_ROW)
End If
Else
M_ROW = Range(M_SHEET & "!" & M_AREA).FindNext(AFTER:=Cells(M_CROW, Ra
M_RANGE = ""
On Error GoTo 100
If M_CBUT = 0 Then GoTo 100
M_code = Trim(M_code)
If M_SHEET = "" Then
M_ROW = Range(M_AREA).FindNext(AFTER:=Cells(M_CROW, Range(M_AREA).Column)).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_COL & M_ROW)
End If
Else
M_ROW = Range(M_SHEET & "!" & M_AREA).FindNext(AFTER:=Cells(M_CROW, Ra
nge(M_AREA).Column)).Row
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_SHEET & "!" & M_COL & M_ROW)
End If
End If
M_CBUT = M_CBUT + 1
M_CROW = M_ROW
100:
S_FINDN = M_RANGE
End Function
If M_COL = "" Then
M_RANGE = M_ROW
Else
M_RANGE = Range(M_SHEET & "!" & M_COL & M_ROW)
End If
End If
M_CBUT = M_CBUT + 1
M_CROW = M_ROW
100:
S_FINDN = M_RANGE
End Function
使用时可在VBA编辑器中新建模块,并将上述函数及全局变量定义复制到该模块即可。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论