VBA宏引用单元格值失效问题：数组填单元格地址时出错

你的代码核心错误出在搜索条件的赋值逻辑上：

• 你写的arr = Array("*" & "(BZ6)" & "*")是把字符串*(BZ6)*直接作为搜索关键词，VBA不会自动识别并解析字符串里的单元格地址，它只会把(BZ6)当成普通文本去匹配，自然找不到目标内容。
• 另外，Find方法里用了LookIn:=xlFormulas，这个参数会让程序搜索单元格的公式内容而非显示值，如果你的目标是匹配单元格显示的文本，应该改成LookIn:=xlValues。

Option Explicit

Sub SearchForString()

    Dim a As Long, arr As Variant, fnd As Range, cpy As Range, addr As String
    Dim searchKeyword As String
    
    On Error GoTo Err_Execute

    ' 读取BZ6单元格的实际值，注意替换成该单元格所在的工作表名称
    searchKeyword = Worksheets("Sheet1").Range("BZ6").Value
    ' 构造带通配符的搜索条件数组
    arr = Array("*" & searchKeyword & "*")

    With Worksheets("Sheet8")
        For a = LBound(arr) To UBound(arr)
            ' 修改LookIn为xlValues，匹配单元格显示值
            Set fnd = .Columns("BT").Find(what:=arr(a), LookIn:=xlValues, LookAt:=xlPart, _
                                         SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                         MatchCase:=False, SearchFormat:=False)
            If Not fnd Is Nothing Then
                addr = fnd.Address
                If cpy Is Nothing Then Set cpy = fnd.EntireRow
                Do
                    Set cpy = Union(cpy, fnd.EntireRow)
                    Set fnd = .Columns("BT").FindNext(after:=fnd)
                Loop Until fnd.Address = addr
            End If
        Next a
    End With

    ' 先判断是否找到匹配内容，避免空对象报错
    If Not cpy Is Nothing Then
        With Worksheets("sheet2")
            cpy.Copy Destination:=.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
        MsgBox "所有匹配数据已复制完成。"
    Else
        MsgBox "未找到匹配数据。"
    End If

    Exit Sub

Err_Execute:
    MsgBox "处理出错：" & Err.Number & " - " & Err.Description
    Debug.Print Now & " " & Err.Number & " - " & Err.Description

End Sub

1. 读取单元格值：新增searchKeyword变量，通过Worksheets("Sheet1").Range("BZ6").Value获取单元格的实际显示值，再拼入搜索条件中。注意要替换成BZ6实际所在的工作表名称，比如如果该单元格在Sheet8，就改成Worksheets("Sheet8")。
2. 调整搜索模式：将LookIn:=xlFormulas改为LookIn:=xlValues，确保搜索的是单元格显示的内容而非公式。
3. 空对象判断：在执行复制前判断cpy是否为空，避免没有匹配结果时触发错误。
4. 错误提示优化：修改错误弹窗的提示内容，区分成功和错误场景，方便排查问题。

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