使用VBA .Find函数查找同行异列字符串时遇命名参数错误求助

问题描述 #

需使用VBA的.Find函数查找两个字符串并定位其所在行，调用示例为rowindex = getrowindex(sysnum, "Wavelength", "Test-Config")。原脚本在执行以下代码时报错**"Named Argument Not Found"**：

Set parameter_row = Worksheets(sysnum).Range("B:B").Find(What:=parametername, What:=routingname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)

已确认可用引用中无Solver.xlam。

原完整VBA代码 #

Main 主过程 #

Public Sub Main()
Dim wb As Workbook, ws As Worksheet, i As Range, dict As Object, sysnum As Integer, sysrow As Integer, syscol As Integer, wsName As String 
Dim wbSrc As Workbook

Dim value As Long, colindex As Long, rowindex As Long

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1") 

Set wbSrc = Workbooks.Open("Q:\Specification and Configuration Document.xlsx")
Set dict = CreateObject("scripting.dictionary")
    For Each i In ws.Range("E2:E15").Cells ' i = every WD number
      sysnum = i.value
      sysrow = i.Row
      syscol = i.Column

      If sysnum = "" Then
        On Error Resume Next
      End If
If Not dict.Exists(sysnum) Then ' 检查唯一值是否已存在，避免重复添加到字典
    dict.Add sysnum, True
    If Not SheetExists(sysnum, ThisWorkbook) Then
        wsName = i.EntireRow.Columns("D").value ' 待复制的工作表名称
        If SheetExists(wsName, wbSrc) Then ' 源工作簿中存在该工作表时执行复制
            wbSrc.Worksheets(wsName).Copy After:=ws ' 复制工作表
            wb.Worksheets(wsName).name = sysnum ' 重命名复制后的工作表
        End If
    Else
        MsgBox "Sheet " & sysnum & " already exists"
    End If
End If

Sheets(1).Select
' 波长调谐范围部分
' 在Sheet1中获取列索引
colindex = getcolumnindex(ws, "Tuning Range (nm)")
value = getjiradata(sysrow, colindex)

' 在SD工作表中获取行索引
rowindex = getrowindex(sysnum, "Wavelength Tuning Range", "Test-Config-OCT")

Next i
End Sub

SheetExists 函数 #

Function SheetExists(SheetName As String, wb As Workbook)
  On Error Resume Next
  SheetExists = Not wb.Sheets(SheetName) Is Nothing
End Function

getcolumnindex 函数 #

Function getcolumnindex(sht As Worksheet, colname As String) As Long 
Dim paramname As Object
Set sht = Worksheets("Sheet1")
Set paramname = sht.Range("A1:Z2").Find(What:=colname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)
    If Not paramname Is Nothing Then
        getcolumnindex = paramname.Column
    End If
End Function

getjiradata 函数 #

Function getjiradata(WDrow As Integer, parametercol As Long) 
   Dim cell As Variant, ws As Worksheet
   cell = getcolumnindex(ws, "Tuning Range (nm)")
   getjiradata = Cells(WDrow, parametercol)
End Function

报错的 getrowindex 函数 #

Function getrowindex(WDnum As Variant, parametername As String, routingname As String) As Long 
   Dim parameter_row As Object
   Set parameter_row = Worksheets(sysnum).Range("B:B").Find(What:=parametername, What:=routingname, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)
      If Not parameter_row Is Nothing Then
          getrowindex = parameter_row.Row
      End If
End Function

报错原因 #

VBA的.Find方法仅允许传入一个What参数用于指定查找内容，原代码中重复设置What:=parametername, What:=routingname属于语法错误，触发"Named Argument Not Found"报错。

更新后的修正 getrowindex 函数 #

通过先查找第一个字符串，再遍历匹配结果并检查相邻单元格是否为第二个字符串的方式实现需求：

Function getrowindex(WDnum As Variant, parametername As String, routingname As String) As Long ' 定位SD工作表中参数所在行
Dim parameter_row As Range, ws As Worksheet, rowname As Range, addr As String

Set ws = ThisWorkbook.Sheets(WDnum)

Set rowname = ws.Columns("B").Find(What:=parametername, Lookat:=xlWhole, LookIn:=xlFormulas, MatchCase:=True)
    If Not rowname Is Nothing Then addr = rowname.Address ' 记录第一个匹配单元格地址，避免无限循环
    Do While Not rowname Is Nothing
        If rowname.Offset(0, 1).Value = routingname Then ' 检查右侧C列是否为目标routingname
            getrowindex = rowname.Row
            Exit Function
        End If
    Set rowname = ws.Columns("B").FindNext(after:=rowname)
        If rowname.Address = addr Then Exit Do ' 回到首个匹配单元格时退出循环
    Loop
 End Function 

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