Compare two complex 2-D Arrays. Need faster Code !

[Ombir]

Good evening Geeks,

I have a Sheet shown below which contains marks of students. I want to compare each Subject and Subject marks of student with a a reference table which contains Subject Name, Min, Max limit of marks and a flag to determine whether a subject is practical or not.

If column Isprac is "Y" then I need to compare sub,th,pr column of each subject with reference table and if subject is found and marks are within max and min limit then total to be calculated in tot column for each subject and student.

If column Isprac is "N" then then I need to compare sub,th column of each subject with reference table and if subject is found and marks are within max and min limit then total to be calculated in tot column for each subject and student.

I have written below code which is working fine but it is very slow as I have large number of records.

Sub compare()
Dim i As Long, j As Long, k As Long, lr As Long, lc As Long, sb As String
Dim ws1 As Worksheet, ws2 As Worksheet
Dim ar(7, 5) As Variant

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 0 To 7
    For j = 0 To 5
        ar(i, j) = ws2.Cells(i + 2, j + 1)
    Next
Next
ws1.Activate
For i = 2 To lr
    For j = 2 To lc Step 4
        sb = Cells(i, j).Value
        th = Cells(i, j + 1).Value
        pr = Cells(i, j + 2).Value
            For k = 0 To 7
                If sb = ar(k, 0) Then
                    If ar(k, 5) = "N" Then
                        If Val(ar(k, 2)) <= Val(th) And Val(th) <= Val(ar(k, 1)) Then
                            tot = Val(th)
                            Cells(i, j + 3).Value = tot
                            Exit For
                        Else
                            Cells(i, j + 3).Value = " "
                        End If
                    Else
                        If (Val(th) >= Val(ar(k, 2)) And Val(th) <= Val(ar(k, 1))) And (Val(pr) >= Val(ar(k, 4)) And Val(pr) <= Val(ar(k, 3))) Then
                            tot = Val(th) + Val(pr)
                            Cells(i, j + 3).Value = tot
                            Exit For
                        Else
                            Cells(i, j + 3).Value = " "
                        End If
                    End If
                End If
            Next
    Next
Next
End Sub

Data Sheet:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Roll	SUB1	TH1	PR1	TOT1	SUB2	TH2	PR2	TOT2	SUB3	TH3	PR3	TOT3	SUB4	TH4	PR4	TOT4
BIO	HIC	PHY	CHE
PHY	HIC	CHE	MAT
ACC	HIC	MAT	HOS
BUS	HIC	PHY	HOS
MAT	HIC	CHE	PHY

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5001[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]34[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5002[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5003[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]37[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5004[/TD]

[TD="align: right"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]85[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]

[TD="align: right"]56[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5005[/TD]

[TD="align: right"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

Reference Table:

	A	B	C	D	E	F
SUB	THMAX	THMIN	PRMAX	PRMIN	ISPRAC
ENC	N
HIC	N
PHY	Y
CHE	Y
MAT	N
HOS	Y
ACC	Y
BUS	N

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

Output Needed:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Roll	SUB1	TH1	PR1	TOT1	SUB2	TH2	PR2	TOT2	SUB3	TH3	PR3	TOT3	SUB4	TH4	PR4	TOT4
BIO	HIC	PHY	CHE
PHY	HIC	CHE		MAT
ACC		HIC		MAT		HOS
BUS	HIC		PHY		HOS
MAT	HIC	CHE		PHY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]5001[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]38[/TD]
[TD="align: right"][/TD]
[TD="align: right"]38[/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]45[/TD]

[TD="align: right"]34[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]49[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5002[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]33[/TD]

[TD="align: right"]42[/TD]
[TD="align: right"][/TD]
[TD="align: right"]42[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]55[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]5003[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]37[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]72[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5004[/TD]

[TD="align: right"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"]44[/TD]

[TD="align: right"]85[/TD]
[TD="align: right"][/TD]

[TD="align: right"]17[/TD]
[TD="align: right"]21[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"]41[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5005[/TD]

[TD="align: right"]74[/TD]
[TD="align: right"][/TD]
[TD="align: right"]74[/TD]

[TD="align: right"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"]77[/TD]

[TD="align: right"]65[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]17[/TD]

</tbody>

Sheet1​

I am new to VBA. I would be grateful if anybody can assist me and provide some faster code with array or similar.

Thank you !

 

[Ombir]

Rothstein, Hiker, Heyden, Peter! Where are you guys? I hope this question hasn't been asked before. Could you lend me a hand here ?

 

[Ombir]

To be precise, I want to replace all my If conditions with 2-3 functions(or as required) so that I could call the function whenever needed. Can anybody help me here please ?