当FIND没有返回结果时函数退出

发布于 2024-10-22 18:32:28 字数 657 浏览 5 评论 0原文

我想要拥有自己的功能，能够在多个工作表上搜索同一区域。我的想法是浏览一组工作表（这里是工作簿中的所有工作表）并使用 Find 搜索该区域。现在，如果在第一张纸上搜索成功，我不希望结果被下面的 if 条件中不成功的搜索覆盖。

Function SheetsFind(LookUpValue As Integer) As Variant
    Dim SearchRange As Range

    For Each WS In Sheets
        Set SearchRange = WS.Range("A1:B6")

        If (SearchRange.Find(LookUpValue, LookIn:=xlValues, LookAt:=xlWhole) <> "Nothing") Then
            SheetsFind = SearchRange.Find(LookUpValue, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Value
        End If
    Next WS

End Function

现在的问题是，如果条件中的查找不成功，则该函数将被保留，并且我会收到 #value 错误。

为什么我的函数不继续下一次迭代？

原文

I want to have my own function, that is able to search the same area on several worksheets. My idea was to go through a set of sheets (here all sheets in the workbook) and use Find to search that area. Now if the search was successful on the first sheet, I don't want the result to be overwritten from the unsuccessful searches on the following, there for the if condition.

Function SheetsFind(LookUpValue As Integer) As Variant
    Dim SearchRange As Range

    For Each WS In Sheets
        Set SearchRange = WS.Range("A1:B6")

        If (SearchRange.Find(LookUpValue, LookIn:=xlValues, LookAt:=xlWhole) <> "Nothing") Then
            SheetsFind = SearchRange.Find(LookUpValue, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Value
        End If
    Next WS

End Function

The problem now is, if the find in the condition is unsuccessful, the function is left and I get a #value error.

Why does my function not just continue with the the next iteration?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风吹短裙飘 2024-10-29 18:32:28

Range.Find 返回另一个范围对象，因此您会遇到问题。

试试这个：

Function SheetsFind(LookUpValue As Integer) As Variant

    Dim SearchRange As Range
    Dim oResult As Excel.Range
    Dim ws As Worksheet

    For Each ws In Sheets
        Set SearchRange = ws.Range("A1:B6")

        Set oResult = SearchRange.Find(LookUpValue, LookIn:=xlValues, LookAt:=xlWhole)

        If Not oResult Is Nothing Then

            SheetsFind = oResult.Offset(0, 1).Value

        End If

    Next ws

End Function

Range.Find returns another range object, for this reason you're getting problems.

Try this:

Function SheetsFind(LookUpValue As Integer) As Variant

    Dim SearchRange As Range
    Dim oResult As Excel.Range
    Dim ws As Worksheet

    For Each ws In Sheets
        Set SearchRange = ws.Range("A1:B6")

        Set oResult = SearchRange.Find(LookUpValue, LookIn:=xlValues, LookAt:=xlWhole)

        If Not oResult Is Nothing Then

            SheetsFind = oResult.Offset(0, 1).Value

        End If

    Next ws

End Function

回复收藏 0 原文

~没有更多了~