Excel VBA- Range.find when range contains leading and trailing spaces

[alpeshjain]

Hi,

I am using a custom function in VBA to check whether a range contains a particular value or not and it works fine. Here is the code -

Function searchValue(fromSheet As Worksheet, value As String, col As String) As Integer    Set findrange = fromSheet.Range(col).Find(What:=Trim(value), LookIn:=xlFormulas, lookat:=xlWhole)
        If findrange Is Nothing Then
            searchValue = 0
        Else
            searchValue = findrange.Row
        End If
End Function

Now the problem I am facing is, the range from where I need to search the values can contains leading/trailing spaces and I want to ignore those leading/trailing spaces while searching for the values. I can take rid this from the value being searched but have no clue of how to ignore/remove these spaces from range being searched.

PS : I am not looking for a for a loop to go cell by cell and trim the range values. I am sure that must be a more elegant solution to this.

Thanks

 

[Fluff]

Depending on what your values are you could change lookat:=xlWhole to lookat:=xlPart
but if you are looking for ran this will also find bran and rang

 

[alpeshjain]

Thanks for response @Fluff, but I am looking for exact match but ignoring leading and trailing spaces.
I read somewhere that Application.Trim() can trim a range, but when tried to use, got an error saying "Type Mismatch", any idea?

 

[Fluff]

AFAIK, the only way to get an exact match (ignoring spaces), would be to loop through the col & trim each cell individually.

 

[FormR]

Hi, here is one possible alternative that you could try. Not sure how advantageous it is over looping and trimming as you go.

Function searchValue(fromSheet As Worksheet, MyValue As String, col As String) As Integer
Dim R As Range
With fromSheet
  Set R = .Range(.Cells(1, col), .Cells(.Rows.Count, col).End(xlUp))
End With
searchValue = Evaluate("IFERROR(MATCH(""" & MyValue & """,TRIM('" & R.Parent.Name & "'!" & R.Address & "),0),0)")
End Function