KenTurner
04-12-2015, 01:18 PM
This is an Excel 2007 issue.
I have a table, Table_1, 6 columns wide, and variable in depth (currently just over 2000 rows deep).
Each cell in each row of Table_1 has an interior.color set to one of 5 preset values, Yellow, Light Blue, Grey, Green, Pink; colours within the row occur strictly in that order, and since there are five possible colours in six cells, there is always at least one duplicate. There can be up to 6 instances of the same colour in one row.
A second table, Patterns, is also 6 columns wide, but just 210 rows deep, and each row contains interior.colors conforming to the same rules as the rows in Table_1. Patterns contains all possible colour combinations and variations. The rows in this table are not in any particular order.
The object of the exercise is to match the colour pattern in each row of Table_1 with a row in Patterns, and return the row number within the Patterns table. The following code works fine, putting the result into column 7 of Table_1:
With [Tabel_1]
m = 0
For Each sRow In .Rows
m = m + 1
p = 0
For Each pRow In [patterns].Rows
p = p + 1
bFound = True
n = 0
For Each pCell In pRow.Cells
n = n + 1
bFound = bFound And (.Cells(m, n).Interior.Color = pCell.Interior.Color)
If Not bFound Then Exit For
Next pCell
If bFound Then
.Cells(m, 7) = p
Exit For
End If
Next pRow
Next sRow
End With
The problem with this solution is that it is relatively slow, due to the large number of iterations. I have spent a fair while trawling the VBA Help and tne Internet for other techniques, but drawn a blank. Can anybody come up with a suggestion?
A secondary issue, a solution to which could potentially speed up the operation a little, concerns the iteration counters (m, n and p in the above example). Is there any way, when using a for...each structure, of determining the row number relative to the top of the table, without using m, n and p? None of my tables starts in absolute row 1, and I need to be able to relocate them without breaking the code.
Incidentally, I coded it initially with simple for...next loops, but by experimentation discovered - somewhat to my surprise - that for...each loops were quicker - even with added iteration counters.
I have a table, Table_1, 6 columns wide, and variable in depth (currently just over 2000 rows deep).
Each cell in each row of Table_1 has an interior.color set to one of 5 preset values, Yellow, Light Blue, Grey, Green, Pink; colours within the row occur strictly in that order, and since there are five possible colours in six cells, there is always at least one duplicate. There can be up to 6 instances of the same colour in one row.
A second table, Patterns, is also 6 columns wide, but just 210 rows deep, and each row contains interior.colors conforming to the same rules as the rows in Table_1. Patterns contains all possible colour combinations and variations. The rows in this table are not in any particular order.
The object of the exercise is to match the colour pattern in each row of Table_1 with a row in Patterns, and return the row number within the Patterns table. The following code works fine, putting the result into column 7 of Table_1:
With [Tabel_1]
m = 0
For Each sRow In .Rows
m = m + 1
p = 0
For Each pRow In [patterns].Rows
p = p + 1
bFound = True
n = 0
For Each pCell In pRow.Cells
n = n + 1
bFound = bFound And (.Cells(m, n).Interior.Color = pCell.Interior.Color)
If Not bFound Then Exit For
Next pCell
If bFound Then
.Cells(m, 7) = p
Exit For
End If
Next pRow
Next sRow
End With
The problem with this solution is that it is relatively slow, due to the large number of iterations. I have spent a fair while trawling the VBA Help and tne Internet for other techniques, but drawn a blank. Can anybody come up with a suggestion?
A secondary issue, a solution to which could potentially speed up the operation a little, concerns the iteration counters (m, n and p in the above example). Is there any way, when using a for...each structure, of determining the row number relative to the top of the table, without using m, n and p? None of my tables starts in absolute row 1, and I need to be able to relocate them without breaking the code.
Incidentally, I coded it initially with simple for...next loops, but by experimentation discovered - somewhat to my surprise - that for...each loops were quicker - even with added iteration counters.