VBA Search for a Value in an Array

When you store values in an array, there could be a time when you need to search within an array. In that case, you need to know the methods that you can use. Now, look at the below code that can help you understand how to search for a value in an array.

1. In the first part of the code, you have variables that you need to use in the code further.
2. After that, the next part generates random numbers by using RND to get you the ten values for the array.
3. Next, an input box let you enter the value that you want to search within the array.
4. After that, you have a line that uses the IF statement to check if the value you have entered in the input box is a number or not.
5. In this part, you have a code for the string to use in the message box if the value you have entered is not found.
6. This part of the code uses a For Loop (For Each) to loop through each item in the array and check if the value that you have entered is in the array or not.
7. The last part of the code shows you a message about whether the value is found or not.

Option Base 1
Sub vba_array_search()

'this section declares an array and variables
'that you need to search within the array.
Dim myArray(10) As Integer
Dim i As Integer
Dim varUserNumber As Variant
Dim strMsg As String

'This part of the code adds 10 random numbers to
'the array and shows the result in the
'immediate window as well.
For i = 1 To 10
myArray(i) = Int(Rnd * 10)
Debug.Print myArray(i)
Next i

'it is an input box that asks
'you the number that you want to find
Loopback:
varUserNumber = InputBox _
("Enter a number between 1 and 10 to search for:", _
"Linear Search Demonstrator")

'it's an if statement that checks for the value that you
'have entered in the input box.
If varUserNumber = "" Then End
If Not IsNumeric(varUserNumber) Then GoTo Loopback
If varUserNumber < 1 Or varUserNumber > 10 Then GoTo Loopback

'message to show if the value doesn't found.
strMsg = "Your value, " & varUserNumber & _
", was not found in the array."

'loop through the array and match each value with the
'the value you have entered in the input box.
For i = 1 To UBound(myArray)
If myArray(i) = varUserNumber Then
strMsg = "Your value, " & varUserNumber & _
", was found at position " & i & " in the array."
Exit For
End If
Next i

'message box in the end
MsgBox _
strMsg, vbOKOnly + vbInformation, _
"Linear Search Result"

End Sub