JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 516
- Office Version
- 365
- Platform
- Windows
Hi All,
I am relatively new to arrays and find this a bit confusing.
I have a table, starting in cell A2, variable depth, but with constant width of 62 columns wide.
From Column N (col 14), I create a list of unique values in Column BL (col 64) -> all good
The idea is to count each incidence in Column N of the unique value in Column BL and output the count result to Column BM (col 54) -> all good, but slow.........
Enter Arrays..... because I am trying to loop one array (unique values), inside another (the raw data), I cant seem to get the syntax right. I am probably doing something most silly...
This is the basic code with all the extra bits removed.
Would somebody, please be so kind as to add the extra bits to turn this into an array sub, so I can compare it against my fruitless efforts.
I am relatively new to arrays and find this a bit confusing.
I have a table, starting in cell A2, variable depth, but with constant width of 62 columns wide.
From Column N (col 14), I create a list of unique values in Column BL (col 64) -> all good
The idea is to count each incidence in Column N of the unique value in Column BL and output the count result to Column BM (col 54) -> all good, but slow.........
Enter Arrays..... because I am trying to loop one array (unique values), inside another (the raw data), I cant seem to get the syntax right. I am probably doing something most silly...
This is the basic code with all the extra bits removed.
Would somebody, please be so kind as to add the extra bits to turn this into an array sub, so I can compare it against my fruitless efforts.
VBA Code:
Sub DualAcceptanceTest()
Dim rngData As Range, rngUniqueData As Range
Dim lngRow As Long, RowCount As Long, lngCount As Long
'Select active sheet
Sheet5.Select 'Input Import
'Set up Data to search
Set rngData = Range("N3", Range("N3").End(xlDown))
'Set up Unique values to look for
RowCount = Range("N4").End(xlDown).Row
Range("BL3").Formula2 = "=unique(N3:N" & RowCount & ")"
Set rngUniqueData = Range("BL3", Range("BL3").End(xlDown))
With rngUniqueData
For lngRow = 1 To .Rows.Count
lngCount = Application.WorksheetFunction.CountIf(rngData, .Cells(lngRow, 1).Value)
.Cells(lngRow, 2).Value = lngCount
If lngCount > 3 Then
.Cells(lngRow, 2).Interior.ColorIndex = 6
End If
Next lngRow
End With
End Sub
