Search VBA excel

[Alcat03]

I am having trouble with this VBA code. I have a reset button that selects 20 random numbers between 1 and 10. Then I have a search button that is supposed to list all the numbers you want to search, but my code is only listing the last row my number that i asked for is found and not listing all the rows the number is found in. How do I code for all the rows the number searched for us listed and output on the excel sheet?

[
Dim num As Integer
Dim i As Integer
Dim location As Integer

num = InputBox("Enter a number to search.")
location = 0
For i = 1 To 10
    If Cells(i, 1) = num Then
        location = i
    End If
Next i

If location = 0 Then
    MsgBox (num & " is not listed.")
Else
    MsgBox (num & " is in row " & location & ".")
End If

/CODE]

 

[rollis13]

Use your macro with these changes:

Option Explicit
Sub test()
    Dim num As Integer
    Dim i As Integer
    Dim location As String                        '<-changed
    num = InputBox("Enter a number to search.")
    location = ""                                 '<-changed
    For i = 1 To 10
        If Cells(i, 1) = num Then location = location & " | " & CStr(i) '<-changed
    Next i
    If location = "" Then                         '<-changed
        MsgBox (num & " is not listed.")
    Else
        MsgBox (num & " is in row " & location & ".") '<-changed
    End If
End Sub

 

[Skyybot]

Try this:

Option Base 1
Sub FindNumber()
Dim num As Integer
Dim i As Integer, x As Integer
Dim location(), numRows As String

num = InputBox("Enter a number to search.")

For i = 1 To 10
    If Cells(i, 1) = num Then
        ReDim Preserve location(x)
        location(x) = i
        x = x + 1
        
    End If
Next i
On Error Resume Next
If UBound(location) = 0 Then
    MsgBox (num & " is not listed.")
Else
    For i = 0 To UBound(location)
        numRows = numRows & location(i) & ", "
    Next i
    numRows = Left(numRows, Len(numRows) - 2)
    MsgBox (num & " is in row " & numRows & ".")
End If
End Sub