Sub FindRowsWithPipeToArray()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim outputRow As Long
Dim cellValue As Variant
Dim results() As Long
Dim resultCount As Long
On Error GoTo ErrorHandler ' 错误处理
' 设置工作表
Set ws = ThisWorkbook.Sheets("Sheet1") ' 请根据需要修改工作表名称
' 确定A列的最后一行
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' 初始化结果数组
ReDim results(1 To lastRow) ' 初始大小,最大行数
' 初始化计数器
resultCount = 0
' 遍历A列的每一行
For i = 1 To lastRow
cellValue = ws.Cells(i, 1).Value
' 检查单元格内容是否包含 '|'
If Not IsEmpty(cellValue) And InStr(cellValue, "|") > 0 Then
resultCount = resultCount + 1
results(resultCount) = i ' 将行号存入数组
End If
Next i
' 将结果写回到B列
If resultCount > 0 Then
ws.Range("B1:B" & resultCount).Value = Application.WorksheetFunction.Transpose(results)
End If
MsgBox "查找完成,包含 '|' 的行号已写入B列。"
Exit Sub
ErrorHandler:
MsgBox "发生错误: " & Err.Description
End Sub
初始化数组:使用 ReDim results(1 To lastRow) 初始化结果数组。最初设定为 lastRow 大小,以确保足够容纳所有可能的行号。