Forum Discussion

Xuejing's avatar
Xuejing
Copper Contributor
Dec 14, 2021

Find function in VBA not working consistently

Hi, 

 

I used a find function in VBA to find a string in a list. The weird thing is the find function doesn't work for ~15% of the strings. I tried to use xlookup on the same list and it doesn't work as well. As an example, I am looking for SSUB in the snapshot on the left in the list on the right. It is obvious that SSUB is present in the list that I am trying to search but it doesn't come up using find in VBA. I tried using xlookup and it doesn't work as well. The find function works well for all others in the list I wanted to find. I am not sure why this is happening. Anyone has this experience? 

 

 

Thanks. 

 

Xuejing 

 

excel
Formulas and Functions
Macros and VBA

3 Replies

  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Bronze Contributor
    Dec 14, 2021
    Can you please post a sample file with the code or just the code so we can assist you?
    • Xuejing's avatar
      Xuejing
      Copper Contributor
      Dec 15, 2021

      Juliano-Petrukio Hi, I sorted that out. My suspect is some entries have a space after the 4th alphabet. After I used left() to extract the 1st 4 lettters, my code worked... 🙂 

      • Juliano-Petrukio's avatar
        Juliano-Petrukio
        Bronze Contributor
        Dec 15, 2021

        Xuejing 

         

        Try using TRIM() as well so you can remove unecessary spaces.

        Sub YourCode()
    'Your Code/Variables
    
    Dim CleanText As String
    CleanText = Trim(YourCriteria)
    
    'Your Code
    
End Sub

        . 

Resources