.Find method does not find strings if the # of spaces do not match. Do I need a RegExp?

I recently helped a forum user on here and ended up trimming leading and trailing spaces with tedious code like what I posted below but I just know there is a better way.

I was using the Find Method with arguments: RNG_AllNames_dest.Find(ValueToFind, After:=WS_dest.Range("A" & LRW_src), LookAt:=xlWhole, MatchCase:=False), with LRW_src as the last row in the range I'm searching.

In order to avoid something like the code below, do I need to use a Regular Expression or is there an argument in the Find method that will ignore trailing and leading spaces in a string I'm searching?

Please Login or Register  to view this content.

If lookup values are unique except the leading or trailing spaces, try changing LookAt:=xlWhole to LookAt:=xlPart and see if that works. Or you may use something like What:=Trim(ValueToFind).