- Public Function nvl(cz, co)
- '多表精确查找,类似于VLOOKUP功能
- 'NVL(查找对象,超找到的内容偏移列数),查找不到返回的是0
- Dim rng As Range, sht As Worksheet
- On Error Resume Next
- For Each sht In Worksheets
- If sht.Name <> ActiveSheet.Name Then
- Set rng = sht.Cells.Find(what:=cz, lookat:=xlWhole).Offset(0, co)
- If Not rng Is Nothing Then
- nvl = rng.Value
- Exit For
- End If
- End If
- Next
- End Function
复制代码
这段自定义函数代码,在查找不到数据的时候,返回的是0.如何实现,像普通函数一样,超找不到的时候返回错误类型?
- Public Function nvl(cz, co)
- '多表精确查找,类似于VLOOKUP功能
- 'NVL(查找对象,超找到的内容偏移列数),查找不到返回的是0
- Dim rng As Range, sht As Worksheet
- On Error Resume Next
- For Each sht In Worksheets
- If sht.Name <> ActiveSheet.Name Then
- Set rng = sht.Cells.Find(what:=cz, lookat:=xlWhole).Offset(0, co)
- If Not rng Is Nothing Then
- nvl = rng.Value
- Exit For
- End If
- End If
- Next
- If rng Is Nothing Then nvl = "查找不到"
- End Function
复制代码可以显示查找不到!
|