时间戳

首页 > 解决方案 > 通过细胞矩阵选择细胞

vba - 通过细胞矩阵选择细胞

问题描述

@TimWilliams，如果我将 CellArray 矩阵定义为范围，那么它会在构建矩阵的代码上崩溃，我按照您在https://stackoverflow.com/a/8320884/11835835链接上发布的答案进行操作

Dim CellsArray(3,3) As Range
For X = 0 To 2  
    For Y = 0 To 2  
        CellsArray(X, Y) = Cells(X+1,Y+1) _
        .Address(RowAbsolute:=False, ColumnAbsolute:=False)  'it crashes here run-time error 91 
    Next Y  
Next X

For K = 1 To 2  
    ActiveSheet.Union(Range(CellsArray(0, 0), CellsArray(0, K))).Select  
Next K

相反，如果我将 CellsArray 矩阵定义为字符串，它会起作用

Dim CellsArray(3,3) As String

但后来它崩溃了

ActiveSheet.Union(Range(CellsArray(0, 0), CellsArray(0, K))).Select

运行时错误 438

标签： vba

解决方案

尝试这个：

Dim CellsArray(1 To 3, 1 To 3) As Range 'easier to use a 1-based array

For X = 1 To 3  
    For Y = 1 To 3  
        Set CellsArray(X, Y) = Cells(X, Y) 'Need Set here
    Next Y  
Next X

I'm not sure what you want to do here...

Dim rng As Range 
For K = 1 To 3 
    If rng is nothing then
        Set rng = CellsArray(1, 1)
    Else
        Set rng = Application.Union(rng, CellsArray(1, K)) 
    End If
Next K
rng.Select

推荐阅读