Changing code from populating real tables in worksheets to arrays to save time

[jayse]

My VBA code populates 3 different sheets in loops by column by column. The first column of first sheet populates from an existing table. Next, the first column of the second sheet looks at the first column of the first sheet and uses that data to make a decision for what it will populate with. Finally, the first column of the third sheet looks at the past two sheets and itself and then populates itself based on that info. The loop begins again, and 200+ columns on 3 sheets are populated.

I want to change this to calculate using arrays instead of physically populating the columns one at a time to speed up processing time.

I was having success until I reached this part of my code:

For Days= 2 To 200
For SerialNum = 2 To 200

If WorksheetFunction.CountA(OrdersSheet.Range(OrdersSheet.Cells(SerialNum, Day), OrdersSheet.Cells(SerialNum, OrderDays))) = 0 Then
OrdersSheet.Cells(SerialNum, OrderDays) = OrderQty

Else
OrdersSheet.Cells(SerialNum OrderDays) = Empty

End If

It looks at certain cells in the sheet to determine whether an order has already been placed using CountA
if an order has not been placed, it populated the cell with the order qty, if it has been placed (if any of the cells are nonzero), then it doesn't place an order

My question is how can I do this with an array instead of range in a worksheet? From my understanding you cannot use countA on an array.

Thanks in advanced

 

[CalcSux78]

Here's an article that may help understand how they work better: The Complete Guide to Using Arrays in Excel VBA - Excel Macro Mastery

Long story short, you can assign a range to an array & loop through the array to drastically improve code performance.

Sub test()
Dim rng As Range
Dim Arr As Variant
Dim R&, C&  'declared as Long


'define the range (many methods to do so)
Set rng = [B2].CurrentRegion
Arr = rng


'array is a matrix of rows and columns now.


For C = LBound(Arr, 2) To UBound(Arr, 2)
    For R = LBound(Arr, 1) To UBound(Arr, 1)
        'do something
    Next R
Next C


End Sub