Hey there, let's break down why you're hitting that Error 91 and how to fix it.

The Root Cause #

Error 91 pops up here because your Find call for bidNum is returning Nothing (meaning it couldn't find a match for your Double value in the column), and then you're trying to access the .Row property of a non-existent object. Even though Find supports Double types, there are a few common reasons it might fail to find your value:

• The target cell might store the number as text instead of a numeric value
• There's a precision mismatch (e.g., the cell has 123.456 but your bidNum is 123.4560001 due to floating-point quirks)
• The Find method is using default parameters that aren't matching your data (like partial matches instead of exact ones)

Fixed & Robust Code #

Here's a revised version of your function with error handling and explicit Find parameters to avoid this issue:

Function getWOMOSO(ByVal bidNum As Double, ByVal partSheet As Worksheet) As Variant
    Dim womoso As Variant
    Dim bidRowRange As Range
    Dim bidCol As Long
    Dim womosoCol As Long
    
    ' Debug to confirm input value
    Debug.Print "Searching for bidNum: " & bidNum
    
    ' Find the BID column first (with error check)
    On Error Resume Next
    bidCol = partSheet.Cells(1, 1).EntireRow.Find(What:="BID", LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    If bidCol = 0 Then
        getWOMOSO = "BID column not found"
        Exit Function
    End If
    
    ' Find the matching bidNum row - capture the range first to check if found
    Set bidRowRange = partSheet.Cells(1, bidCol).EntireColumn.Find( _
        What:=bidNum, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False)
    
    ' Check if we found a match
    If bidRowRange Is Nothing Then
        getWOMOSO = "No match found for bidNum: " & bidNum
        Exit Function
    End If
    
    ' Find WO/MO/SO column (with error check)
    On Error Resume Next
    womosoCol = partSheet.Cells(1, 1).EntireRow.Find(What:="WO/MO/SO", LookIn:=xlValues, LookAt:=xlWhole).Column
    On Error GoTo 0
    If womosoCol = 0 Then
        getWOMOSO = "WO/MO/SO column not found"
        Exit Function
    End If
    
    ' Get the value and return it
    womoso = partSheet.Cells(bidRowRange.Row, womosoCol).Value
    getWOMOSO = womoso
End Function

Key Improvements #

• Capturing the Range object: Instead of directly accessing .Row, we store the result of Find in a Range variable first. This lets us check if bidRowRange Is Nothing to handle missing matches gracefully.
• Explicit Find parameters: Adding LookIn:=xlValues and LookAt:=xlWhole ensures we're matching exact values in cell contents, not formulas or partial strings.
• Error checking for column searches: We also add checks to make sure the "BID" and "WO/MO/SO" columns exist, so you get clear feedback if those headers are missing.
• Return type changed to Variant: This lets us return either the numeric value or an error message, making debugging easier.

Additional Tips #

• If your bidNum values might have precision issues, consider converting both the search value and cell values to a string with a fixed number of decimal places before searching.
• Make sure the worksheet you're passing to partSheet is actually active or properly referenced (though your original code handles this correctly by passing the worksheet explicitly).

内容的提问来源于stack exchange，提问作者Northern Cub