基于条件创建命名区域时出现运行时错误

Question

我有以下代码通过一个表。当它在 C 列中找到单词"报告"和在 B 列中找到"OSI"时，它会为列 B 和 C 中的条件为真实的每一行创建一个名为"OSIRep"的范围，从 D-E-F 列开始。

但是，当我将单词更改为"安全性"和"OSI"以创建范围"OSISec"时，我收到一个错误，我不知道为什么。错误是"运行时错误 1004 - 对象'_Worksheet'的方法'范围'失败"，这发生在 sht 上。范围(sRng(

它正在搜索的范围没有什么不同，我尝试创建的名称尚不存在，我的条件存在。有什么想法吗？

我尝试对工作表进行保护，解锁单元格并探索错误代码，但都无济于事。

提前感谢您提供的任何帮助或见解！

Set sht = ThisWorkbook.Worksheets("Features")
Set featuresRng = sht.Range(sht.Range("B1"), sht.Range("C" & sht.Rows.Count).End(xlUp))
rngArray = featuresRng
ReDim NewArr(1 To 1)
y = 1
For i = 1 To UBound(rngArray)
    If rngArray(i, 2) = "Reporting" And rngArray(i, 1) = "OSI" Then
        ReDim Preserve NewArr(1 To y)
        NewArr(y) = featuresRng.Rows(i).Resize(1, 3).Offset(0, 2).Address
        y = y + 1
    End If
Next i
sRng = Join(NewArr, Application.DecimalSeparator)
ThisWorkbook.Names.Add "OSIRep", sht.Range(sRng)

Answer 1

根据我的评论，Range只接受 255 个字符的地址字符串，因此最好在循环时处理实际的 Range 对象：

For i = 1 To UBound(rngArray)
    If rngArray(i, 2) = "Reporting" And rngArray(i, 1) = "OSI" Then
         Dim OutputRange As Range
         If OutputRange Is Nothing Then
            Set OutputRange = featuresRng.Rows(i).Resize(1, 3).Offset(0, 2)
         Else
            Set OutputRange = Union(OutputRange, featuresRng.Rows(i).Resize(1, 3).Offset(0, 2))
         End If
    End If
Next i
If Not OutputRange Is Nothing Then OutputRange.Name = "OSIRep"

Answer 2

您不需要 featuresRng 对象，因为您可以直接处理工作表，因为您事先知道正在处理的列。以下是实现目标的有效方法：

Sub Macro1()
Dim sht As Worksheet, MySel As Range, rngArray As Variant, i As Long
Set sht = ThisWorkbook.Worksheets("Features")
With sht
    rngArray = .Range("B1", .Range("C" & .Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(rngArray)
        If rngArray(i, 2) = "Reporting" And rngArray(i, 1) = "OSI" Then
            If MySel Is Nothing Then
                Set MySel = .Range("D" & i & ":F" & i)
            Else
                Set MySel = Application.Union(MySel, .Range("D" & i & ":F" & i))
            End If
        End If
    Next i
End With
If Not MySel Is Nothing Then MySel.Name = "OSIRep"
'Set MySel = Nothing
End Sub