Excel VBA数组范围的循环 [英] Excel VBA Array Ranges for a loop

问题描述

我试图获取一个循环函数来引用一系列单元格，而不是单独输入工作表名称.

I'm trying to get a loop function to refer to a range of cells, rather than typing in the worksheet names individually.

我有下面的代码可以正常工作:

I've got this code that works:

Sub LoopTest()
'
' Macro4 Macro
'

'
' Loop Test

Dim SheetsArray As Variant
Dim i As Integer
SheetsArray = Array("Bun1", "Bun2")

For i = LBound(SheetsArray) To UBound(SheetsArray)
    With Worksheets(SheetsArray(i)).Select
        Range("A2:G60").Select
        Selection.ClearContents
    End With
Next i

End Sub

但是当我尝试使其引用一个范围时，此代码就没有了:

But then this code doesn't when I try and get it to refer to a range instead:

Sub LoopTest()
'
'
' Loop Test

Dim Arr As Variant
Dim i As Integer
Dim rng As Range

Set rng = Worksheets("Names").Range("A2:A3")

Arr = rng

For i = LBound(Arr) To UBound(Arr)
    With Worksheets(Arr(i)).Select
        Range("A2:G60").Select
        Selection.ClearContents
    End With
Next i

End Sub

当我尝试使用它时，出现下标超出范围"错误，但是工作表全部存在(该范围与第一个代码具有相同的名称).调试突出显示了

I'm getting "Subscript out of range" error when I try and use it, however the worksheets all exist (the range has the same names as the first code). Debug is highlighting the

WIth Worksheets(Arr(i)).Select

行.任何帮助将不胜感激！

line. Any help would be greatly appreciated!

-Cr1kk0

推荐答案

将范围分配给变体时，始终会得到一个二维数组，因此需要指定两个维度.也无需在这里选择任何内容:

You always get a 2 dimensional array when you assign a range to a variant so you need to specify both dimensions. There's also no need to select anything here:

For i = LBound(Arr, 1) To UBound(Arr, 1)
    Worksheets(Arr(i, 1)).Range("A2:G60").ClearContents
Next i

这篇关于Excel VBA数组范围的循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！