1

In Excel 2010, I am trying to get this small script to work.

 If searchVal = findVal Then
    Set rngX = Worksheets("NEW Format").Range("C:C").Find(What:=resultVal, LookIn:=xlValues)
    ID = rngX.Offset(0, -1).Value
 End If

I know the 'searchVal' is correct, I know the 'findVal' is correct, and I know the 'resultVal' is correct. Also, rngX is defined as a range. However, each time this runs, the rngX is always Nothing. The ID should be set as the row where 'resultVal' is found, one cell to the left.

I tried it a different way and the oucome was exactly the same.

 On Error Resume Next
 If searchVal = findVal Then
    Set rngFindValue = ActiveSheet.Range("C1:C100").Find(What:=resultVal, After:=ActiveSheet.Range("C1"), LookIn:=xlFormulas)
    ID = rngFindValue.Address
    ID = rngFindValue.Offset(0,-1)
 End If

I looked at the link below and none of those samples work for me.

http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=112:find-method-in-excel-vba-find-multiple-occurrences-find-method-to-vlookup-find-date&catid=79&Itemid=475

I must be missing something really simple here, but I don't know what it is!

Editing my OP: I tried this as well.

 If searchVal = findVal Then
    Range("C2:C" & LRow).Select
    For Each cell In Selection
        If cell = resultVal Then
            ID = cell.Offset(0, -1).Value
        End If
    Next
 End If

In the Immediate Window I can see that cell = resultVal. I still don't understand why this is not working.

I just found something interesting. The two variables seem to be different when I examine them in the Watch Window.

enter image description here

It looks like there is a trailing space after the second variable, even though I put a TRIM() around it. When I look at both variables in the Immediate Window, both appear to be exactly the same. Maybe the issue is due to the data type...not sure...

Community
  • 1
  • 1
ASH
  • 20,759
  • 19
  • 87
  • 200
  • are you searching for dates? What's your current setting for LookAt, is it xlWhole or xlPart? – cyboashu Jan 26 '17 at 16:28
  • 1
    Have you tried `.Find(resultVal,range("c1"),Xlvalues,XlLookAt.xlWhole,,xlNext,false).address` Is it the whole of the cell value or part of it? see XlLookat – Nathan_Sav Jan 26 '17 at 16:29
  • It's not even a date or any weird characters. I'm just trying to find this string: 'Lending Attestation'. I tried xlWhole AND xlPart. Neither worked. – ASH Jan 26 '17 at 16:41
  • Are you absolutely sure the value of `resultVal` is what you think it is ? – Tim Williams Jan 26 '17 at 16:58
  • 1
    Per your edit, yes, the trailing space will definitely make a difference :) . – cxw Jan 26 '17 at 17:50
  • It's possible the space is not a space but something else, so did not get stripped by Trim() – Tim Williams Jan 26 '17 at 18:11

2 Answers2

3

When you use the find functionality (either in VBA or in Excel GUI) the application saves the search preferences that you last used. For this reason when you're working with .Find() in VBA it's always a good idea to explicitly set these search parameters.

We know that your .Find() isn't working because rngX is Nothing - The usual trip-up is by not setting the LookAt:= parameter which defines if you are looking for a full or partial match in the search range.

Finally, get rid of On Error Resume Next - you should never use that for "fixing" problems, only for dealing with an error that you've already trapped and handled.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • 1
    Also... add a check for `rngX Is Nothing` before you access the object reference, so you can handle a "not found" situation instead of dealing with a runtime error 91. – Mathieu Guindon Jan 26 '17 at 16:41
  • I just updated my OP. I tried another method and it still doesn't work. I really don't understand what is going on here. This seems very weird to me. – ASH Jan 26 '17 at 16:46
  • 2
    `really don't understand what is going on here.` quite simply, whatever you're searching for, with the settings you have provided, isn't being found. – SierraOscar Jan 26 '17 at 16:48
  • @ryguy72 specify all parameters. As this answer says, `Range.Find` "remembers" parameter values from previous calls (makes Ctrl+F more "user-friendly" / convenient I guess), so if you want reliable results you need to specify all parameters, explicitly, always. – Mathieu Guindon Jan 26 '17 at 16:56
  • When I look in the WatchWindow I see this: resultVal = "Update Fair Lending Tollgate Information " and cell = "Update Fair Lending Tollgate Information". So, it looks like there is a trailing space at the end of resultVal but when I go to the source on the sheet, there is no trailing space. I did TRIM() on both variables and these two variables still do not match. I'm not sure what this means...Range.Find "remembers" parameter values from previous calls. Can someone please explain? Thanks. – ASH Jan 26 '17 at 17:24
  • are you getting the search value from the internet or a web page? – SierraOscar Jan 26 '17 at 17:25
  • I figured it out. There was a Chr(10) appended to the end of 'resultVal'. Damn! – ASH Jan 26 '17 at 19:15
  • @ryguy72 that's why I was asking if t was from a web page ;) – SierraOscar Jan 26 '17 at 19:16
  • 1
    Actually, it is 100% Excel. In my area people love to do Alt+Enter (literally Chr(10) in VBA) to separate things in cells. I added in the Chr(10) to accommodate a new ask, and then forgot it was there. As always, computers do exactly what you tell them to do. Anyway, thanks for the help everyone!! – ASH Jan 26 '17 at 22:34
  • no worries, glad you got it sorted. fwiw, the reason I asked about web pages is because they have a nasty habit of having non-displaying characters in there that cause nightmares with VBA. Something just to keep in mind for the future anyway. – SierraOscar Jan 26 '17 at 22:35
  • To be exact, Find "remembers" some parameters. Not all – brettdj Jan 27 '17 at 07:57
1

You could use Clean to cater for this (removing the linefeed)

Also, worth looking at Remove leading or trailing spaces in an entire column of data

And from here

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method

code

Dim resultVal As String
Dim rng1 As Range

resultVal = "mystr" & Chr(10)
resultVal = Application.Clean(resultVal)

Set rng1 = ActiveSheet.Range("C1:C100").Find(resultVal, ActiveSheet.Range("C1"), xlFormulas, xlWhole)
If Not rng1 Is Not Nothing Then
    ID = rng1.Address
End If
Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177