Search value in VBA

[mehidy1437]

Hello Guys

I'm facing an issue with searching value in excel in VBA.
When I search a value, if the value only exist in a1 then below code give me the resul a1.
But if same value exist in a1 & in any other cells like in a2 or b2, it gives me the result b2 or a2 i/o a1.

Why is this happening?

Sub SearchValues()
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    
    searchValue = "Style"
    Set searchRange = Range("A:Z")
    
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchDirection:=xlNext, SearchOrder:=xlByRows, SearchFormat:=False)
    
    If Not foundCell Is Nothing Then
        MsgBox "Value found in cell " & foundCell.Address
    Else
        MsgBox "Value not found in the search range."
    End If
End Sub

 

[Akuini]

The Find method actually begin the search after the first cell (i.e A1), in this case cell B1.
Try this:

Sub SearchValues()
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundCell As Range
   
    searchValue = "Style"
    Set searchRange = Range("A:Z")
    If UCase(Range("A1")) Like "*" & UCase(searchValue) & "*" Then
         MsgBox "Value found in cell " & Range("A1").Address
    Else
        Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchDirection:=xlNext, SearchOrder:=xlByRows, SearchFormat:=False)
       
        If Not foundCell Is Nothing Then
            MsgBox "Value found in cell " & foundCell.Address
        Else
            MsgBox "Value not found in the search range."
        End If
    End If
End Sub

 

[mehidy1437]

Thanks, it's working fine.
If find start searching after a1, then why the code in #1, give me the result a1, when the value "Style" is not present in another cell except a1?

 

[Akuini]

If find start searching after a1, then why the code in #1, give me the result a1, when the value "Style" is not present in another cell except a1?

Here's why:
Search starts from B1 > then to the right, namely c1, d1, ....z1 (the last column in Range("A:Z")) > then to the next row > this process continues until the last row then returns to A1.
So actually A1 is the last cell to search.

 

[mehidy1437]

Here's why:
Search starts from B1 > then to the right, namely c1, d1, ....z1 (the last column in Range("A:Z")) > then to the next row > this process continues until the last row then returns to A1.
So actually A1 is the last cell to search.

Many thanks for the details information.

 

[Akuini]

You're welcome.
There's another way to do it, which is using "After" argument, but you need to know where the last cell is.
For example, say the search range is Range("A1:Z100"):

Sub SearchValues1()
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    
    searchValue = "Style"
    Set searchRange = Range("A1:Z100")

        Set foundCell = searchRange.Find(What:=searchValue, After:=Range("Z100"), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchDirection:=xlNext, SearchOrder:=xlByRows, SearchFormat:=False)
        
        If Not foundCell Is Nothing Then
            MsgBox "Value found in cell " & foundCell.Address
        Else
            MsgBox "Value not found in the search range."
        End If

End Sub

the search will start at A1.

 

[mehidy1437]

Great, thanks a lot.