Excel ActiveSheet.Shapes.Range(MyArray)。选择

Excel ActiveSheet.Shapes.Range(MyArray).Select

提问人:sylviaaaaa 提问时间:11/1/2023 最后编辑:sylviaaaaa 更新时间:11/2/2023 访问量:37

问:

我想在指定的页码上选择具有指定条件的形状,一次将它们选择到一个数组中,并将它们分组到组中,但系统显示“ActiveSheet.Shapes.Range(MyArray)”的错误消息。选择”

Sub group_all() 

    Dim X, x_count, x_start, x_end As Integer
    Dim S As Shape
    Dim MyArray
    Dim n As Integer
    
    With ActiveSheet

        X = 2
    
         If X = 1 Then
            x_start = 1
        Else:
            x_start = .HPageBreaks(X - 1).Location.Row 'x start row
         End If
         x_end = .HPageBreaks(X).Location.Row - 1 'X end row
        '------------------------------------------------------------------------------------------------------
        MyArray = Array("")

        For Each S In .Shapes
        
            If S.Type = msoGroup Then S.Ungroup

        Next S
        
        n = 0
        
        For Each S In .Shapes

            If (S.Top > Cells(x_start, 10).Top And S.Top < Cells(x_end, 10).Top + Cells(x_end, 10).Height) And InStr(S.Name, "CommandButton") = 0 Then
            
                MyArray = Split(Join(MyArray, ",") & IIf(n = 0, "", ",") & S.Name, ",")
                
                n = n + 1
                
            End If

        Next S
    
    End With
    

    'Array("TextBox 4173", "TextBox 4164", "TextBox 4165", "TextBox 4174")
    ActiveSheet.Shapes.Range(MyArray).Select
    Selection.ShapeRange.Group.Select

End Sub

我该如何解决,谢谢。

数组 Excel

评论


答:

1赞 Black cat 11/1/2023 #1

Shapes.Range接受数组作为参数,但它的索引必须以 1 开头。

因此,将其插入到代码的语句之后。End With

ReDim c(1 To UBound(MyArray) + 1)
For i = 1 To UBound(MyArray) + 1
c(i) = MyArray(i - 1)
Next i

并更改参数

    ActiveSheet.Shapes.Range(c).Select