Formula to get set of rows numbers where match criteria

[Fractalis]

Hello to all,

I have this table in range A1:C7:

Header1	Header2	Header3
98	3	462
77	2	77
65	8	23
65	2	982
2	5	11
44	2	35

<tbody>
</tbody>

- A formula that gives me a set of row numbers of all occurences of 2 in column B, result should be {3,5,7}
- and how to store that result in an VBA array to use it in a macro.

In this example, there are 3 occurences, so, the set result has 3 elements. But if there are
50 occurences, the result set should have 50 elements.

I hope is possible.

Thanks in advance for any help.

 

[ChrisR]

Hi,

Is this what you are looking for? This will loop through looking at the data on sheet 1, and any row in column B that it finds a 2 it will copy the row number onto Sheet 2. The loop works by counting how many rows there are, so this should solve the issue as to sometimes there are 3, whilst other times there are 50 etc.

Regards,
Chris

Sub Findrows()
Dim RC As Integer
Dim RL As Integer
Dim RS As Integer
RC = Sheets("Sheet1").UsedRange.Count
RS = 0
For RL = 1 To RC
Sheets("Sheet1").Cells(RL,2).Select
If ActiveCell.Value = 2 Then
RS = RS + 1
Sheets("Sheet2").Cells(RS, 1).Value = RL
Else
End If
Next RL
End Sub

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Cell As Range
    Dim i As Long
    Dim Arr() As Long
    For Each Cell In Range("B2:B7")
        With Cell
            If .Value = 2 Then
                i = i + 1
                ReDim Preserve Arr(1 To i)
                Arr(i) = .Row
            End If
        End With
    Next Cell
End Sub

 

[Fractalis]

Hello Chris and Andrew,

Many thanks for your help, your codes do what I need without using a spreedsheet formula.

Thank you again.

Regards

 

[ChrisR]

Hi Fractalis,

That's great. Thanks for the feedback.

Regards,
Chris