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 wo...
excel
Formulas and Functions
Macros and VBA
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