Making an array and searching for specific values

[stakar]

I have the following 2 columns (these are examples)
A
----
22
00
01
00
01
10
07
00
15

and column B
B
---
00

I want to pass the data of column A in an array, then i want to search
for each value within the array, that is the next in position of
column's B value.
in the particular example i m searching for the next in position value
of '00' (the '<-- ' values are the result)
A
22
00
01<--
00
01<--
10
07
00
15<--
00
01<--

Then i want to create a column with the unique values in sort order of
the above '<--' values and next to them to count the appearance

In the particular example i m searching for the next in position value
of '00' (the '<-- ' values are the result)
C D
-------
01 3
15 1

So we get the value '01' to appears 3 times and the value '15' to
appears 1 time.
I want all of them to be made using arrays because i want speed and
also the data will be written in an existing worksheet.

Thanks in advance
Stathis

 

[BrianB]

This seems to work OK. There would be no benefit to the use of array
here :-

1. A worksheet is already in array format.
2. Time would be taken putting numbers to the arrays and taking the
out to display.
3. Sorting data in a worksheet is much faster than sorting an array.
4. The ability to visibly check results ensures accuracy and cu
programming time.

'----------------------------------------------------
Sub test()
Dim TestVal
Dim MyRange As Range
Dim FromRow As Long
Dim ToRow As Long
Dim Result
Dim ResultCount As Long
Dim Counter As Long
'----------------------------------
Application.Calculation = xlCalculationManual
r = ActiveSheet.Range("A1").End(xlDown).Row
Set MyRange = ActiveSheet.Range(Cells(1, 1), Cells(r, 1))
TestVal = Range("B1").Value
ToRow = 1
For FromRow = 1 To MyRange.Rows.Count
If MyRange.Cells(FromRow, 1).Value = TestVal Then
'- use column Z as scratch area
ActiveSheet.Range("Z" & ToRow).Value = _
MyRange.Cells(FromRow + 1, 1).Value
ToRow = ToRow + 1
End If
Next
'- sort results
ResultCount = ToRow
ActiveSheet.Range("Z1:Z" & ResultCount).Sort Key1:=Range("Z1"), _
Order1:=xlAscending, Header:=xlNo
FromRow = 2
ToRow = 1
Result = ActiveSheet.Range("Z1").Value
Counter = 1
'- results to columns C & D
Do
If ActiveSheet.Range("Z" & FromRow).Value = Result Then
Counter = Counter + 1
Else
ActiveSheet.Cells(ToRow, 3).Value = Result
ActiveSheet.Cells(ToRow, 4).Value = Counter
Result = ActiveSheet.Range("Z" & FromRow).Value
Counter = 1
ToRow = ToRow + 1
End If
FromRow = FromRow + 1
Loop While FromRow <= ResultCount
Application.Calculation = xlCalculationAutomatic
End Sub
'---------------------------------------------------

 

[stakar]

Brian Thanks
It works perfect