vb.net 使用excel范围查找返回类型不匹配。（来自 HRESULT 的异常：0x80020005 (DISP_E_TYPEMISMATCH)）

using excel range find return Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

提问by E R

I'm working on Excel add-on I need to search Values on cell (selected by user) in Column (Selected by user) and write result in cell (selected by user)

我正在使用 Excel 插件我需要在列（由用户选择）中的单元格（由用户选择）上搜索值并将结果写入单元格（由用户选择）

Ex: if cell A2 exist in column B in rows 5, 10, and 15 result in C2 must be 5,10,15

例如：如果单元格 A2 存在于 B 列的第 5、10 和 15 行，则 C2 必须为 5、10、15

I Face error when trying to find Row using this code

尝试使用此代码查找 Row 时遇到错误

result = sheetName.Cells.Find(cellVal, SearchRange, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, _
                                       Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _
                                       Type.Missing, Type.Missing, Type.Missing).Row.ToString

and with this code too

还有这个代码

    result = sheetName.Cells.Find(What:=cellVal, LookIn:=SearchRange.Value, SearchOrder:=SearchRange.Rows, _
                  SearchDirection:=Excel.XlSearchDirection.xlNext, MatchCase:=False, SearchFormat:=False).ToString

My Full Code here

我的完整代码在这里

Dim xlapp As Excel.Application = Globals.ThisAddIn.Application
Dim sheetName As Excel.Worksheet
sheetName = Globals.ThisAddIn.Application.ActiveSheet

Dim LastDataRow As Integer = sheetName.Range(cmbCheckDataCol.Text & sheetName.Rows.Count).End(Excel.XlDirection.xlUp).Row
Dim LastCheckInRow As Integer = sheetName.Range(cmbCheckRngCol.Text & sheetName.Rows.Count).End(Excel.XlDirection.xlUp).Row
Dim SearchRange As Excel.Range = sheetName.Range(cmbCheckRngCol.Text & "2", cmbCheckRngCol.Text & LastCheckInRow.ToString)
' --------------------------------- start check
Dim cellVal, result As String
For rowNum As Integer = 2 To LastDataRow

    cellVal = sheetName.Range(cmbCheckDataCol.Text & rowNum.ToString).Value.ToString   ' if null will return Error
    'result = xlapp.WorksheetFunction.CountIf(SearchRange, cellVal)                  ' search
    result = sheetName.Cells.Find(cellVal, SearchRange, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, _
                               Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _
                               Type.Missing, Type.Missing, Type.Missing).Row.ToString

I get error Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) when trying to assign row number to result variable

我收到错误类型不匹配。（来自 HRESULT 的异常：0x80020005 (DISP_E_TYPEMISMATCH)）尝试将行号分配给结果变量时

采纳答案by E R

I Temporary changed my code to below code and it works with me

我临时将我的代码更改为以下代码，它适用于我

   Dim LastDataRow As Integer = sheetName.Range(cmbCheckDataCol.Text & sheetName.Rows.Count).End(Excel.XlDirection.xlUp).Row
    Dim LastCheckInRow As Integer = sheetName.Range(cmbCheckRngCol.Text & sheetName.Rows.Count).End(Excel.XlDirection.xlUp).Row
    Dim SearchRange As Excel.Range = sheetName.Range(cmbCheckRngCol.Text & "2", cmbCheckRngCol.Text & LastCheckInRow.ToString)
    ' --------------------------------- start check
    Dim cellVal As String = ""
    Dim result As String = "In Row"
    Dim Curentfind As Excel.Range = Nothing
    Dim FirstFind As Excel.Range = Nothing       ' for try microsoft
    For rowNum As Integer = 2 To LastDataRow
        ' ---- try microsoft
        Try
            cellVal = sheetName.Range(cmbCheckDataCol.Text & rowNum.ToString).Value.ToString   ' if null will return Error
            Dim ifExist As Long = xlapp.WorksheetFunction.CountIf(SearchRange, cellVal)
            If ifExist > 0 Then      ' start get rows
                Curentfind = SearchRange.Find(cellVal, , _
                              Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
                              Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, False)
                Dim fFind As String = Curentfind.Address.ToString       ' record find first address to avoid infiniti loop
                For ce As Integer = 1 To LastCheckInRow
                    result &= ", " & Curentfind.Address.ToString.ToUpper
                    Curentfind = SearchRange.FindNext(Curentfind)
                    If Curentfind.Address.ToString = fFind Then Exit For ' breakloop , find back to first address
                Next
                result = Replace(result, "$", "")
            Else
                result = "Not Exist"
            End If

        Catch ex As Exception
            result = "Error, No data in Cell " & cmbCheckDataCol.Text.ToUpper & rowNum.ToString
        End Try
        ' write result
        sheetName.Range(cmbCheckResltCol.Text & rowNum.ToString).Value = result
        result = "In Row"


    Next