VBA looping parallely through array

问题内容:

I am trying to create a loop through specifc sheets and colums which are linked specifically, but this doesn’t work, am I misunderstanding how to use arrays?

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

basically I want loop in a parallel fashion through these associated values ((big, 47),(sr,23),(oou,58)) having the first as the source sheet and the second the column number of a target sheet.
any help?

问题评论:

1  
Hint, you Option Explicit at the top, it will give you so many errors in your code.

答案:

答案1:

Variable declarations: big, rc, sr As Worksheet means sr As Worksheet, while rc and sr are Variant.Also, you are not Diming x anyehere. If you use Option Explicit at the top of your code >> the VBA editor would “scream” an error.

Next: if you want to use arr, and later the loop through it in parallel, you need to define and set arr as a 2-D array, and read rejcheck and copyto array values to it.

Code

Option Explicit

Sub test()

Dim wb As Workbook
Dim big As Worksheet, rc As Worksheet, sr As Worksheet, oou As Worksheet
Dim rejcheck As Variant, copyto As Variant, arr As Variant, x As Variant
Dim i As Long

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)

' define 2-D array according to size of rejcheck array
ReDim arr(0 To UBound(rejcheck), 0 To 1)

' loop through the elements and insert to 2-d array (1 of sheets, second of numeric values)
For i = LBound(rejcheck) To UBound(rejcheck)
    Set arr(i, 0) = rejcheck(i) ' <-- use Set when adding Worksheet object
    arr(i, 1) = copyto(i)
Next i

For i = LBound(arr, 1) To UBound(arr, 1)
    With arr(i, 0)
        .Range("A2").Copy wb.Sheets("other sheet").Cells(1, arr(i, 1))
        wb.Sheets("other sheet").Cells(1, arr(i, 1)).Offset(0, 1).Value = .Name
    End With
Next i

End Sub

答案评论:

答案2:

You can’t create an array and treat it like a Worksheet. And you don’t need to put the two arrays in an array. In the end, it looks like you want to do something like:

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

答案评论:

1  
Actually you can, use a 2-D array, 1 of worksheets and the other of numeric values. But your code works as well 🙂
    
Sure you can create that, but if rejcheck is an array it makes no sense to go With rejcheck .Range...

原文地址:

https://stackoverflow.com/questions/47751311/vba-looping-parallely-through-array

添加评论

友情链接:蝴蝶教程