Function ylookup(y,tar1 As Range,tar2 As Range) Dim r1 As Range,i%,j% For Each r1 In tar1 i = i+ 1If r1 = y Then Exit ForNext r1For Each r1 In tar2j = j + 1If i = j Then ylookup = r1:Exit ForNext r1End Function这个中的参数Y可以换成是范围
来源:学生作业帮助网 编辑:六六作业网 时间:2024/11/29 01:28:40
Function ylookup(y,tar1 As Range,tar2 As Range) Dim r1 As Range,i%,j% For Each r1 In tar1 i = i+ 1If r1 = y Then Exit ForNext r1For Each r1 In tar2j = j + 1If i = j Then ylookup = r1:Exit ForNext r1End Function这个中的参数Y可以换成是范围
Function ylookup(y,tar1 As Range,tar2 As Range) Dim r1 As Range,i%,j% For Each r1 In tar1 i = i
+ 1
If r1 = y Then Exit For
Next r1
For Each r1 In tar2
j = j + 1
If i = j Then ylookup = r1:Exit For
Next r1
End Function
这个中的参数Y可以换成是范围,而不是具体的某个数值吗?麻烦改下VBA.
Function ylookup(y,tar1 As Range,tar2 As Range) Dim r1 As Range,i%,j% For Each r1 In tar1 i = i+ 1If r1 = y Then Exit ForNext r1For Each r1 In tar2j = j + 1If i = j Then ylookup = r1:Exit ForNext r1End Function这个中的参数Y可以换成是范围
y可以直接带入一个单元格地址,比如:
=ylookup(A1,D5:D17,B1:B18)
y只有一个数值,表示只查找一个数值,
如果改成范围的话,会返回多个数值,想要什么样的结果显示呢?
先查找第一个y值,如果有就停止查找,返回找到的结果?如果没有,再查找第二个值?
---------------
Function mlookup(y As Range,tar1 As Range,tar2 As Range)
'y值1个或多个单元格,tar1单列,tar2单列
Application.Volatile
Dim tmp,i&,s$
s = "|"
For Each tmp In y
s = s & tmp & "|"
Next
If Replace(s,"|","") = "" Then tmp = "":GoTo 1000
If tar1.Columns.Count 1 Then tmp = "#tar1":GoTo 1000
If tar2.Columns.Count 1 Then tmp = "#tar2":GoTo 1000
For i = 1 To tar1.Cells.Count
If tar1(i) "" Then
If InStr(1,s,"|" & tar1(i) & "|") Then tmp = tar2(i):GoTo 1000
End If
Next
1000:mlookup = tmp
End Function