VBA looping parallely through array

Question

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?

Hint, you Option Explicit at the top, it will give you so many errors in your code. — Shai Rado
– Shai Rado, Commented Dec 11, 2017 at 11:26
have you tested any of the solutions you got below ? are any of them work as you intended ? — Shai Rado
– Shai Rado, Commented Dec 12, 2017 at 6:11
hi Shai and itslex, thank you very much for your feedback! both solutions seem to work with me, let me try resetting the code in its context. thank you very much also for point out about declarations issue! very help — lorenzo
– lorenzo, Commented Dec 12, 2017 at 8:08
update: itslex version definitely works, while Shai seems to fail as feeding the copyto. do you have any clue? thank you both again anyway! — lorenzo
– lorenzo, Commented Dec 12, 2017 at 8:49

itsLex · Accepted Answer · 2017-12-11 13:54:38Z

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

edited Dec 11, 2017 at 13:54

answered Dec 11, 2017 at 11:47

itsLex

7861 gold badge5 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shai Rado Over a year ago

Actually you can, use a 2-D array, 1 of worksheets and the other of numeric values. But your code works as well :)

itsLex Over a year ago

Sure you can create that, but if rejcheck is an array it makes no sense to go With rejcheck .Range...

Shai Rado · Accepted Answer · 2017-12-11 11:53:18Z

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

Collectives™ on Stack Overflow

VBA looping parallely through array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related