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

alpeshjain

New Member
Joined
Oct 5, 2017
Messages
7
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 -

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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2017
Messages
7
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows
AFAIK, the only way to get an exact match (ignoring spaces), would be to loop through the col & trim each cell individually.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,466
Office Version
  1. 365
Platform
  1. Windows
Hi, here is one possible alternative that you could try. Not sure how advantageous it is over looping and trimming as you go.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,459
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top