我目前正在开发一个函数,该函数将根据我拥有的字符串输入搜索行号。但是在下面的函数中,我得到了标题中所述的错误,我不知道该怎么办:L
这是查找行函数
Function MPNTRowECU(ByVal ECUVariantName) As Range
Debug.Print ECUVariantName
Dim wsMPNT As Worksheet
Set wsMPNT = Worksheets("Module Part Number Tracker")
MPNTRowECU = wsMPNT.Range("C:C").Find(What:=ECUVariantName, LookIn:=xlValues, SearchOrder:=xlByRows)
End Function
Debug.Print 行用于确保 ECUVariantName 确实是一个字符串,并且它确实返回我每次都有的字符串。在MPNTRowECU是我得到错误的地方。有什么建议吗?对 VBA 相当陌生
尝试下面的函数代码,而不是返回Range
,我修改了您的Function
以返回Row
数字(如您的帖子中所述(。
另外,您需要确保Find
是成功的,通过使用If Not MPNTRng Is Nothing Then
标准。
法典
Function MPNTRowECU(ByVal ECUVariantName) As Long
Dim MPNTRng As Range
Debug.Print ECUVariantName
Dim wsMPNT As Worksheet
Set wsMPNT = Worksheets("Module Part Number Tracker")
Set MPNTRng = wsMPNT.Range("C:C").Find(What:=ECUVariantName, LookIn:=xlValues, SearchOrder:=xlByRows)
If Not MPNTRng Is Nothing Then ' confirm that Find was successful
MPNTRowECU = MPNTRng.Row
Else ' Find failed to find "ECUVariantName"
MPNTRowECU = -10000 ' just put a high negative value to raise an error
End If
End Function