Loopy Loops! Problem with loops & arrays

[teefy]

Hello all,

So per my earlier thread, I'm trying to automate the classification of sales based on a matrix.

I want to make a UDF to go in column 'I' of the Sales sheet, that takes the cell values in columns 'A' to 'F' of each row, and compares to each row in the matrix:

Sales

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	SH Ctry	Product	PH 1	PH2	Pkg	Customer	Sales KG	NS GBP	ABC
2	GB	Apples	10004	90002	PB	12753	900	600
3	GB	Pears	10004	90004	PB	12437	450	375
4	IE	Lemons	10003	90064	CB	12743	1800	1000
...

Excel Workbook
	A	B	C	D	E	F	G
1	ABC	SH Ctry	Product	PH 1	PH2	Pkg	Customer
2	A			10004	90002
3	B				90004
4	B	FR
5	A	IE
Matrix

I've created the code below which takes an input from the 'Sales' sheet and puts them into an array. It also loads each row from the 'Matrix' into an array.

Its supposed to compare the corresponding fields from 'Sales' with 'Matrix' and in the event that all fields defined in the 'Matrix' match the 'Sales' data, the respective ABC classification is put into a 'Results' array (rArray).

I hope that makes sense. The code is below, but I can't figure out why in the event of a match, the result isn't being put into the rArray...

Option Explicit
Sub Classify()
' Note: This will ultimately be a Function, not a Sub.
 
' Legend:
' i = Input; m = Matrix; r = Result
 
Dim iArray(), mArray(), rArray()
Dim iRange As Range, iMaxCol As Long, iColCount As Long, iTargetCell As Range
Dim mRange As Range, mMaxRow As Long, mMaxCol As Long, mRowCount As Long, mHitCount As Long, mColCount As Long, mTargetRow As Range, mTargetCell As Range
Dim rLoopCount As Long, rHitCount As Long
 
'Note: In the Function, these will be declared outside of the VBA (sheet refs use codename)
Set iRange = Range(Sales001.Cells(2, 1), Sales001.Cells(2, 6))
Set mRange = Range(Matrix001.Cells(2, 1), Matrix001.Cells(5, 7))
 
iMaxCol = iRange.Columns.Count
mMaxRow = mRange.Rows.Count
mMaxCol = mRange.Columns.Count
 
If Not iMaxCol + 1 = mMaxCol Then
    MsgBox "Incompatible input and matrix ranges.", vbCritical, "Error"
    GoTo Finish
End If
 
'Loads Input Array
ReDim iArray(iMaxCol)
iColCount = 0
 
For Each iTargetCell In iRange.Cells
    iColCount = iColCount + 1
    iArray(iColCount) = iTargetCell.Value
Next iTargetCell
 
'Loads Matrix Array
ReDim mArray(mMaxCol)
mRowCount = 0
 
For mRowCount = 1 To mMaxRow
    Set mTargetRow = mRange.Rows(mRowCount)
    mColCount = 0
    mHitCount = -1
    For Each mTargetCell In mTargetRow.Cells
        mColCount = mColCount + 1
        mArray(mColCount) = mTargetCell.Value
        If Not mArray(mColCount) = 0 Then
            mHitCount = mHitCount + 1
        End If
    Next mTargetCell
 
    'Evaluate Input & Matrix Arrays and Load Result Array
    ReDim rArray(mMaxRow)
    rLoopCount = 0
    rHitCount = 0
 
    For rLoopCount = 1 To iMaxCol
        If iArray(rLoopCount) = mArray(rLoopCount + 1) Then
            rHitCount = rHitCount + 1
        End If
    Next rLoopCount
 
    If rHitCount = mHitCount Then
        rArray(mRowCount - 1) = mArray(1)
    End If
 
Next mRowCount
 
MsgBox rArray(1)  '<< WHY IS THIS BLANK? IT SHOULD RETURN "A" FOR ROW #1 OF THE MATRIX
 
'Next step is to sort the rArray in alphabetic order (re-use code from other project).
'Take position #1 of the array as the result of the function.
Finish:
End Sub

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>Any ideas where I'm going wrong?? Many thanks in advance </o>

 

[teefy]

Solved it - was the positioning of the ReDim of the rArray() within the loop that was killing it each time. D'oh!