Not finding value in VBA

[kweaver]

Without posting the confidential data and without creating a fake sample (which I might have to do tomorrow if no one can understand this)...

I'm trying to find the row in a column where the SearchedValue (which is an email address, if that matters), is found (or not) in column A of the OLD sheet.
Using the following section of code. If/When the email is found, another value from the 5th column of the "OLD" sheet is put into another sheet.
The code is finding about 90% of the emails and not all of them for some reason. There aren't any extra spaces in the SearchedValue nor the column A of OLD being searched.

Am I just ignoring something simple and obvious to someone? Hope you spot my error. Thanks in advance.

For j = 2 To lro
     If StrComp(Sheets("OLD").Range("A" & j).Value, SearchedValue, vbTextCompare) = 0 Then
          MatchingRow = j
          Exit For
     End If
Next j
Sheets("Adj").Cells(r, "E") = Sheets("OLD").Cells(j, 5)

 

[Coding4Fun]

It appears to be correct, is lro what you expect when you run it? Assuming its properly setting lro to your last used row in the range to search.

Are the missed values at the end of the list?

 

[Micron]

Suggest you Debug.Print the addresses ("A" & j) if there's not too many, or copy the ones that are not getting found to a new sheet to create a smaller list and then do that. You might find discrepancies between the searched value and the search term value.
I don't suppose you have set Option Compare Binary in the module?

 

[kweaver]

It appears to be correct, is lro what you expect when you run it? Assuming its properly setting lro to your last used row in the range to search.

Are the missed values at the end of the list?

Yes, lro is the last row of OLD. And the missing values are all over the place.

 

[kweaver]

Would there be a better way to find the row and, if not found, maybe return a 0?

 

[kweaver]

Even this is missing some of the values (with g as variant):

SearchedValue = Sheets("Adj").Cells(i, "A")
g = Application.VLookup(SearchedValue, Sheets("OLD").Range("A1:E600"), 5, False)
If Not IsError(g) Then
 Sheets("Adj").Cells(r, "E") = g
 Else
End If

But a VLOOKUP in the sheet works just fine.

 

[Peter_SSs]

Would there be a better way to find the row and, if not found, maybe return a 0?

I would not be looping to look, or using a worksheet function. Rather I would look directly for the relevant value using something like this. I have assumed that MatchingRow has been declared as a numeric value (Long?) and has not previously been assigned any value

Dim rFound As Range

Set rFound = Sheets("OLD").Columns("A").Find(What:=SearchedValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not rFound Is Nothing Then MatchingRow = rFound.Row

Debug.Print MatchingRow

As to why apparently equal values are not being found, my suspicion is that in fact the values are not equal (possibly invisible non-printing characters).
Assuming SearchedValue is read from a worksheet cell, you could test with a worksheet formula two values you think are the same eg

=EXACT(A2,OLD!A4)

 

[kweaver]

I would not be looping to look, or using a worksheet function. Rather I would look directly for the relevant value using something like this. I have assumed that MatchingRow has been declared as a numeric value (Long?) and has not previously been assigned any value

Dim rFound As Range

Set rFound = Sheets("OLD").Columns("A").Find(What:=SearchedValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not rFound Is Nothing Then MatchingRow = rFound.Row

Debug.Print MatchingRow

As to why apparently equal values are not being found, my suspicion is that in fact the values are not equal (possibly invisible non-printing characters).
Assuming SearchedValue is read from a worksheet cell, you could test with a worksheet formula two values you think are the same eg

=EXACT(A2,OLD!A4)

I thought of that earlier and check and they are exact.

 

[kweaver]

I may have found my issue...referencing the incorrect row.
Am double-checkng in the morning because I can't see straight now.

Thanks to all for making suggestions and comments.