VBA loop to replace 5k+ array formulas and circular referencing

[ka123]

Hi,

I have built a model which uses array formulas to pull up Invoice IDs and quantities from "Data" tab to "Shipments" tab. Invoice IDs correspond to each Part (Col A) for dates after those listed in Invoice_date column (Col B). This is a multiple occurrence case which continues to look for invoices until we reach the qty specified in Col E of the shipments tab and then moves on to the next line. We can't have any duplicate invoices for each Part (Col A), hence I'm using the last invoice on the row above for the same part as the beginning date for the next invoice. This is resulting in circular referencing and really slowing down the processing.

Can someone please assist me with a VBA macro to tackle this? I have pulled out a few lines as an example in the attached.
Many thanks in advance!

Kay

[SamT]

this doesn't help much
New-1.jpg
Click to open image in new tab

[ka123]

Have you enabled iterative calculations in your Excel?
I have re-attached the attachment, it shouldn't show the "NAME" error, see below screenshot.

Screen Shot 2016-07-02 at 12.45.28 AM.jpg

[offthelip]

I am not entirely sure of your requirements and what I have done doesn't work perfectly but I thought I would post it now because I have run out of time. It might point you in the right direction. I have added an extra sheet called test to avoid overwriting the "shipments" tab.

[vba]Sub movedata()
Worksheets("DATA").Select
inarr = Range(Cells(1, 1), Cells(1321, 4))
Worksheets("Shipments").Select
datar = Range(Cells(1, 1), Cells(28, 5))
Worksheets("test").Select
Range(Cells(1, 1), Cells(28, 350)) = ""
outarr = Worksheets("test").Range(Cells(1, 1), Cells(28, 350))
' loop round the part numbers asnd quantities on the shipments tab
For i = 2 To 28
' loop through all the incvoices to mathc them up
pcount = 0
outcol = 6
For j = 4 To 1321
If datar(i, 2) = inarr(j, 1) Then
' part number matches
outarr(i, 2) = datar(i, 2)
outarr(i, 5) = datar(i, 5)
pcount = pcount + inarr(j, 4)
outarr(i, outcol) = inarr(j, 3)
outcol = outcol + 1
If pcount >= datar(i, 5) Then
' end of row save the date
If i < 28 Then
outarr(i + 1, 3) = inarr(j, 2)
pcount = 0
outcol = 6
i = i + 1
End If

End If
End If
Next j
Next i


Worksheets("test").Range(Cells(1, 1), Cells(28, 350)) = outarr


End Sub


[/vba]