arrays - VBA通过数组并行循环

Question

我正在尝试通过特定链接的特定工作表和列创建一个循环，但这不起作用，我是否误解了如何使用数组？

Sub test()

Dim wb As Workbook, big, rc, sr  As Worksheet, rejcheck, copyto, arr As variant
Set wb = ActiveWorkbook
Set big = wb.Sheets("BIG")
Set oou = wb.Sheets("OOU")
Set sr = wb.Sheets("SR")

rejcheck = Array(big, sr, oou)
copyto = Array(47, 23, 58)
arr = Array(rejcheck, copyfrom)

For Each x In arr
    With rejcheck
        .Range("a2").Copy wb.Sheets("other sheet").Cells(1, copyto)
        wb.Sheets("other sheet").Cells(1, copyto).Offset(0, 1).Value = .Name
    End With
Next x

End Sub

基本上我希望通过这些关联值以并行方式循环 ((big, 47),(sr,23),(oou,58))将第一个作为源工作表，第二个作为目标工作表的列号。
有什么帮助吗？

Answer 1

您不能创建数组并将其视为工作表。而且您不需要将两个数组放在一个数组中。最后，您似乎想要执行以下操作:

Option Base 0

Sub test()
  Dim wb As Workbook, big, oou, sr  As Worksheet, rejcheck, copyto, x As Variant
  Dim i As Integer
  Set wb = ActiveWorkbook
  Set big = wb.Sheets("BIG")
  Set oou = wb.Sheets("OOU")
  Set sr = wb.Sheets("SR")

  rejcheck = Array(big, sr, oou)
  copyto = Array(47, 23, 58)

  For i = 0 To UBound(rejcheck)
    With rejcheck(i)
      .Range("a2").Copy wb.Sheets("other sheet").Cells(1, copyto(i))
      wb.Sheets("other sheet").Cells(1, copyto(i)).Offset(0, 1).Value = .Name
    End With
  Next
End Sub