Как насчёт этого?

Private Sub CommandButton2_Click()
Dim iNumber As Integer
Dim iArray() As Integer
Dim iIndex As Integer
Dim iCount As Integer



iNumber = Val(InputBox("Number:"))
If iNumber = 0 Then Exit Sub

For iIndex = 1 To 100
If Val(Range("A" & iIndex)) = iNumber Then
ReDim Preserve iArray(iCount)

iArray(iCount) = iIndex
iCount = iCount + 1
End If
Next

MsgBox "Found " & UBound(iArray) & " items"
End Sub