VBA搜索功能遇1004运行时错误，iDatabaseRow相关问题求助

错误核心原因与修正点 #

触发1004运行时错误的直接原因是代码拼写失误，同时存在多处语法、变量引用问题，逐一修正如下：

• X1Up拼写错误：应为xlUp（字母l，不是数字1），这是iDatabaseRow行报错的核心原因
• AApplication多余前缀：多写了一个A，正确写法是Application
• Copy方法语法错误：Copy.shSearchData.Range写法违规，需指定Destination参数
• 未定义变量iSearch：实际应使用已声明的iSearchRow
• 列表框列数设置错误：Column = 10需改为ColumnCount = 11（对应A到K共11列）
• Match函数参数错误：若下拉框cmbSearchColumn存储的是列标题文本（如"PO"），无需转成CLng，直接匹配文本即可

修复后的完整代码 #

Sub SearchData()
    Application.ScreenUpdating = False
    
    Dim shDatabase As Worksheet 'Database Sheet
    Dim shSearchData As Worksheet 'SearchData Sheet
    
    Dim icolumn As Integer 'To hold the selected column number in database sheet
    Dim iDatabaseRow As Long 'To store the last non blank row number available in Database sheet
    Dim iSearchRow As Long ' To hold the last non blank row number in SearchData sheet
    
    Dim sColumn As String 'To store the column selection
    Dim sValue As String 'To hold the search text value
    
    Set shDatabase = ThisWorkbook.Sheets("Database")
    Set shSearchData = ThisWorkbook.Sheets("SearchData")
    
    ' 修复拼写错误，改用更可靠的最后一行获取方式
    iDatabaseRow = shDatabase.Cells(shDatabase.Rows.Count, "A").End(xlUp).Row
    sColumn = frmForm.cmbSearchColumn.Value    
    sValue = frmForm.txtSearch.Value
    
    ' 修复前缀错误，移除不必要的CLng转换
    icolumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:K1"), 0)
    
    'Remove filter from Database worksheet
    If shDatabase.FilterMode = True Then
        shDatabase.AutoFilterMode = False
    End If
    
    'Apply Filter on Database worksheet
    If frmForm.cmbSearchColumn.Value = "PO" Then
        shDatabase.Range("A1:K" & iDatabaseRow).AutoFilter Field:=icolumn, Criteria1:=sValue
    Else
        shDatabase.Range("A1:K" & iDatabaseRow).AutoFilter Field:=icolumn, Criteria1:="*" & sValue & "*"
    End If
    
    If Application.WorksheetFunction.Subtotal(3, shDatabase.Range("C:C")) >= 2 Then
        'Code to remove the previous data from search data worksheet
        shSearchData.Cells.Clear
        ' 修复Copy方法语法错误
        shDatabase.AutoFilter.Range.Copy Destination:=shSearchData.Range("A1")
        
        Application.CutCopyMode = False
        
        iSearchRow = shSearchData.Range("A" & Application.Rows.Count).End(xlUp).Row
        
        ' 修复列表框列数设置
        frmForm.lstDatabase.ColumnCount = 11
        frmForm.lstDatabase.ColumnWidths = "60,60,75,40,60,45,55,70,70,70,70"
        
        ' 修复变量引用错误
        If iSearchRow > 1 Then
            frmForm.lstDatabase.RowSource = "SearchData!A2:K" & iSearchRow
            MsgBox "Records Found"
        End If
    Else
        MsgBox "No Record Found"
    End If
    
    shDatabase.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

额外优化建议 #

• 若A列存在空行，建议改用更鲁棒的最后一行获取逻辑：

  iDatabaseRow = shDatabase.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  

• 为避免Match函数找不到匹配项报错，可添加错误处理：

  On Error Resume Next
  icolumn = Application.WorksheetFunction.Match(sColumn, shDatabase.Range("A1:K1"), 0)
  On Error GoTo 0
  If icolumn = 0 Then
      MsgBox "指定列不存在"
      Exit Sub
  End If
  

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