0

Is there a way to trim the parameters of a search using Find prior to the search?

I have an Excel file that I would like to search through to find a cell with a particular word in it; each cell contains only one word. The Find function is case-insensitive and works perfectly for the most part. The only (possible) flaw that I have found is that the cell must contain ONLY the word without leading and trailing spaces.

For example: If I want to search for the word acoustic using the Find function 

Dim rng as Range
rng = ActiveSheet.Range("A1:K100")
Set foundItem = rng.Find("acoustic")
if not foundItem Is Nothing Then MsgBox foundItem.Address

This will find the word in its many variations - "Acoustic", "aCOUstic", "acouSTic", etc. but will NOT find it if it has a leading or trailing space, such as " acoustic" or "acoustic ".

So, is there a way to (easily) trim the spaces off the cell prior to performing the search? Or will I have to create a function to go through the range and trim first?

Blackwood
  • 4,504
  • 16
  • 32
  • 41
Apolymoxic
  • 730
  • 14
  • 34

2 Answers2

1

just set LookAt parameter of Find() method to xlPart

Set foundItem = rng.Find(What:="acoustic", LookIn:=xlValues, lookat:=xlPart, MatchCase=False)

you can also make the same search in formulas:

Set foundItem = rng.Find(What:="acoustic", LookIn:=xlFormulas, lookat:=xlPart, MatchCase=False)

and be informed that you should always explicitly specifcy LookIn, LookAt, SearchOrder, and MatchByte parameters, otherwise Find() will use their last settings even if from Excel UI! (I found it sufficient for my purposes to always specify at least LookIn and LookAt parameters)

user3598756
  • 28,893
  • 4
  • 18
  • 28
0

You can trim the range prior to finding:

rng.value = Application.Trim(rng.value)

Or if the range has formulas and you want to keep them:

rng.Formula = Application.Trim(rng.Formula)
A.S.H
  • 29,101
  • 5
  • 23
  • 50
  • Wow.... you are all over this VB stuff. I knew you could trim, but had no idea you could trim an entire range that way. This is perfect. Thank you! – Apolymoxic Feb 18 '17 at 15:14
  • @Apolymoxic you're welcome. You can take a look [here](http://stackoverflow.com/questions/42147564/trim-function-remove-spaces-from-cells-using-vba/42148229#42148229) for different methods to trim a range. – A.S.H Feb 18 '17 at 15:17