工作簿中的此功能曾经有效。它在另一个工作簿中查找 A:A 列中项目主工作表上的一个范围,并返回找到的零件的范围。
现在Set FindRow
失败了,弹出一个提示“下标超出范围”的窗口。单击“帮助”会提供一些信息,但我无法在此处应用它。任何帮助都将不胜感激。
Function FindPartNumber(ByVal Part As String, ByVal mpl_wb As Workbook) As Range
Dim FindRow As Range
Set FindRow = mpl_wb.Worksheets("Item Master").Range("A:A").Find(What:=Part, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=True)
If Not FindRow Is Nothing Then
Set FindPartNumber = FindRow
Else
Set FindPartNumber = Nothing
End If
End Function
答案1
尝试验证两个函数参数,并检查返回的对象Nothing
顶部Sub
是一个测试,说明如何检查Function
Option Explicit
Public Sub TestPart()
Dim result As Range
Set result = FindPartNumber(123, ThisWorkbook) 'Make sure that "result" is Set
If Not result Is Nothing Then Debug.Print result.Address 'Check result object
End Sub
'If String/Workbook params are missing, or part is not found, this returns "Nothing"
Public Function FindPartNumber(ByVal part As String, ByVal mplWb As Workbook) As Range
Dim findRow As Range, ws As Worksheet
If mplWb Is Nothing Or Len(part) = 0 Then Exit Function 'Invalid file (mplWb)
With mplWb
On Error Resume Next 'Expected error: sheet name not found (sheet doesn't exist)
Set ws = .Worksheets("Item Master")
If Not ws Is Nothing Then
With ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
Set findRow = .Find(What:=part, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=True)
If Not findRow Is Nothing Then Set FindPartNumber = findRow
End With
End If
End With
End Function
。
笔记
为了使函数更加通用(可重用),将所有硬编码部分移到外面
Option Explicit
Public Sub TestPart()
Dim ws As Worksheet, result As Range, searchRange As Range
Set ws = ThisWorkbook.Worksheets("Item Master")
Set searchRange = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
Set result = FindPartNumber(123, searchRange)
If Not result Is Nothing Then Debug.Print result.Address
End Sub
'If String/Range params are missing, or part is not found, this returns "Nothing"
Public Function FindPartNumber(ByVal part As String, ByVal rng As Range) As Range
Dim findRow As Range
If rng Is Nothing Or Len(part) = 0 Then Exit Function 'Invalid search range or part
Set findRow = rng.Find(What:=part, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=True)
If Not findRow Is Nothing Then Set FindPartNumber = findRow
End Function