Array Copy to Range Loop

[JJorsi]

Having trouble with the code below. Purpose is to sort data from one sheet into an array to match certain data to a range in another sheet in order to copy over missing data. Everything works fine and the first array copies into the first range of data on the new sheet but it then loops back to the beginning of the sub.. Basically the code never ends as a result.. Help or suggestions??

Code:
Sub attendName(fromsh As Variant, tosh As Variant)
'copy attendee names over to match with proper titles

Dim fmArr1(33 To 49, 2) As String
Dim fmArr2(33 To 49, 2) As String
Dim toArr1(33 To 49, 2) As String
Dim toArr2(33 To 49, 2) As String

'filling array with data from from and to sheet
For i = 33 To 49
fmArr1(i, 0) = Sheets(fromsh).Range("$C$" & i).Value
fmArr1(i, 1) = Sheets(fromsh).Range("$D$" & i).Value
fmArr1(i, 2) = Sheets(fromsh).Range("$E$" & i).Value
fmArr2(i, 0) = Sheets(fromsh).Range("$I$" & i).Value
fmArr2(i, 1) = Sheets(fromsh).Range("$J$" & i).Value
fmArr2(i, 2) = Sheets(fromsh).Range("$K$" & i).Value

toArr1(i, 0) = Sheets(tosh).Range("$C$" & i).Value
toArr1(i, 1) = Sheets(tosh).Range("$D$" & i).Value
toArr1(i, 2) = Sheets(tosh).Range("$E$" & i).Value
toArr2(i, 0) = Sheets(tosh).Range("$I$" & i).Value
toArr2(i, 1) = Sheets(tosh).Range("$J$" & i).Value
toArr2(i, 2) = Sheets(tosh).Range("$K$" & i).Value

Next i

'matches attendee titles between sheets and copies the name into array
For i = LBound(fmArr1) To UBound(fmArr1)

For j = LBound(fmArr1) To UBound(fmArr1)​

If fmArr1(i, 0) = toArr1(j, 0) And fmArr1(i, 0) <> blank Then​

toArr1(j, 2) = fmArr1(i, 2)
​

ElseIf fmArr2(i, 0) = toArr1(j, 0) And fmArr2(i, 0) <> blank Then​

toArr1(j, 2) = fmArr2(i, 2)
​

ElseIf fmArr1(i, 0) = toArr2(j, 0) And fmArr1(i, 0) <> blank Then​

toArr2(j, 2) = fmArr1(i, 2)
​

ElseIf fmArr2(i, 0) = toArr2(j, 0) And fmArr2(i, 0) <> blank Then​

toArr2(j, 2) = fmArr2(i, 2)
​

End If​

Next j​

Next i

​

'paste array names into to sheet
Sheets(tosh).Range("$C$33:$E$49").Value = toArr1 'code hits this point, copies fine and then loops back to beginning of sub...
Sheets(tosh).Range("$I$33:$K$49").Value = toArr2 'code never reaches this point

End Sub

 

[Chihiro]

I don't see anything that would loop back in your code...

By chance is this procedure invoked via Worksheet_Change event?

 

[JJorsi]

No, this code is in the Module and is called when a button triggers migration from one sheet to another. It isn't linked to the Worksheet_Change event as far as I can tell..

I don't know what is triggering the loop either, it fills in either range fine but loops before it fills in the second range...

 

[JJorsi]

Update: this code is triggered by the Worksheet_Change event.. this calls a different sub that calls a second sub that then calls my code..

 

[Chihiro]

Hmm, I'd imagine that infinite loop can be avoided by setting Application.EnableEvents to false at start of Worksheet_Change event and turning it back to True at the end.

My guess is that infinite loop is caused by Worksheet_Change being triggered when data array is written to range.value