I have to match both columns below. To get a match you need to sum/combine some values in TC column and combine some values in AE column. (e.g-1 sum of Items 1 & 2 of TC Column (20,378) will match to the sum of items 2 & 3 of AE Column (20,378) e.-2 sum of items 15,18,19 of TC Column will match to the sum of items 12, 14, 15, 16, 20 of AE column). No single amount to sum values matching. To get a match you always need to sum values in both columns.
In my original code, these columns are stored in two different arrays. So I have to perform the loop matching inside these arrays. Hence, the reason why my code begins with storing the values in two arrays below.
This is what I want to achieve in my loop:
IF TCval 1 + TCval 3 = AEval1 + AEval2 + AEvall3 etc.. Then
Color the matched cells in yellow and remove the matched values in both arrays then re-perform the loop again.
Thanks for the help.
In my original code, these columns are stored in two different arrays. So I have to perform the loop matching inside these arrays. Hence, the reason why my code begins with storing the values in two arrays below.
This is what I want to achieve in my loop:
IF TCval 1 + TCval 3 = AEval1 + AEval2 + AEvall3 etc.. Then
Color the matched cells in yellow and remove the matched values in both arrays then re-perform the loop again.
Thanks for the help.
| ITEM | TC | AE | |
| TOTAL | 434,507.00 | 434,507.00 | |
1 | 10,123.00 | 17,205.00 | |
2 | 10,255.00 | 5,223.00 | |
3 | 10,344.00 | 15,155.00 | |
4 | 10,488.00 | 12,234.00 | |
5 | 10,564.00 | 2,234.00 | |
6 | 10,621.00 | 3,467.00 | |
7 | 10,732.00 | 34,567.00 | |
8 | 10,888.00 | 22,354.00 | |
9 | 10,999.00 | 20,609.00 | |
10 | 11,021.00 | 1,245.00 | |
11 | 11,109.00 | 8,893.00 | |
12 | 11,200.00 | 23,672.00 | |
13 | 11,311.00 | 21,087.00 | |
14 | 8,829.00 | 12,958.00 | |
15 | 22,839.00 | 23,049.00 | |
16 | 1,123.00 | 54,432.00 | |
17 | 29,205.00 | 7,890.00 | |
18 | 154,202.00 | 14,654.00 | |
19 | 33,743.00 | 23,490.00 | |
20 | 44,911.00 | 96,673.00 | |
21 | 13,416.00 |
VBA Code:
Sub test1()
Dim tcArrayV() As Variant
Dim aeArrayV() As Variant
Dim GetTcIndex As Long, a As Integer, b As Integer, h As Integer, i As Integer
Dim tcSize As Long, tcIndex As Long
Dim aeSize As Long, aeIndex As Long
ReDim tcArrayV(tcSize)
ReDim aeArrayV(aeSize)
Dim aeRow As Long
Dim tcRow As Long
tcSize = 1: tcIndex = 0: aeSize = 1: aeIndex = 0
aeRow = Worksheets("Sheet1").Cells(Rows.Count(), 4).End(xlUp).Row
tcRow = Worksheets("Sheet1").Cells(Rows.Count(), 2).End(xlUp).Row
'Store all in array
For a = 3 To aeRow
aeArrayV(aeIndex) = CDec(Worksheets("Sheet1").Cells(a, 4).Value)
aeSize = aeSize + 1
ReDim Preserve aeArrayV(aeSize)
aeIndex = aeIndex + 1
Next a
For b = 3 To tcRow
tcArrayV(tcIndex) = CDec(Worksheets("Sheet1").Cells(b, 2).Value)
tcSize = tcSize + 1
ReDim Preserve tcArrayV(tcSize)
tcIndex = tcIndex + 1
Next b
For h = 0 To UBound(tcArrayV) - 2
GetTcIndex = (UBound(tcArrayV) - LBound(tcArrayV) + 1) - 2 ' 'Get total number of entries
For i = 0 To UBound(aeArrayV) - 2
'what to do here?
Next h
End Sub
