这是我的代码，用于查找B列中的单元格并更新Z列中的状态。

Sub FindAndWrite()
    Dim FindValues() As String
    Dim WriteValue As String
    Dim FoundCell As Range
    Dim LastRow As Long
    Dim i As Integer
    Dim j As Integer
    Dim FSO As Object
    Dim TS As Object
    Dim ConcatenateRange As Range
    Dim ExternalWorkbook As Workbook
    Dim ExternalData As Variant
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set TS = FSO.OpenTextFile("C:\Users\2093960\Desktop\tobewritten.xlsx", 1) 'replace "C:\path\to\file.txt" with the path to your file
    
    FindValues = Split(TS.ReadAll, vbCrLf) 'read the values from the file and store them in an array
    TS.Close
    
    WriteValue = "Completed" 'replace "value_to_write" with the value you want to write to column Z
    
    LastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row 'get the last row of column B
    
    Set ExternalWorkbook = Workbooks.Open("C:\Users\2093960\Desktop\tobewritten.xlsx") 'replace with the path to your external file
    ExternalData = Replace(Replace(ExternalWorkbook.Sheets("Sheet1").Range("A1").Value, "-", "_"), " ", "") 'modify this line to access the cell with your data and replace hyphens with underscores and remove spaces
    
    For i = 1 To LastRow 'loop through each row in column B
        Set ConcatenateRange = Range("A" & i & ":C" & i) 'change this to the range of cells containing your data
        Set FoundCell = ActiveSheet.Range("B" & i)
        For j = 0 To UBound(FindValues) 'loop through each value in the array
            If InStr(Replace(Join(Application.Transpose(ConcatenateRange.Value), ""), "-", "_"), Replace(FindValues(j), "-", "_")) > 0 Or InStr(Replace(ExternalData, "-", "_"), Replace(FindValues(j), "-", "_")) > 0 Then 'check if the current value from the array is in the concatenated range or external data
                FoundCell.Offset(0, 25).Value = WriteValue 'write the WriteValue to column Z in the same row
                Exit For 'exit the inner loop when a match is found
            End If
        Next j
    Next i
    
    ExternalWorkbook.Close SaveChanges:=False 'close the external workbook without saving changes
End Sub

我从另一个Excel工作簿中获得的要查找的数据有连字符和下划线。
我得到
无效过程或参数错误

If InStr(Replace(Join(Application.Transpose(ConcatenateRange.Value), ""), "-", "_"), Replace(FindValues(j), "-", "_")) > 0 Or InStr(Replace(ExternalData, "-", "_"), Replace(FindValues(j), "-", "_")) > 0 Then 'check if the current value from the array is in the concatenated range or external data

我尝试了多种方法，包括更改数据类型。