Help with Application.Match and it's funkiness

[gimme_this_gimme_that]

I have data where the exact option of Application.Match always returns
Error when (even when there's a match) and the less-than-exact option
of Application.Match always returns true (even when there's not a
match).

I have the following clip of code:

'less-than-exact option
res = Application.Match(Trim(ad.Cells(i, 1).Value, PSKeyRng)

'exact option
res = Application.Match(ad.Cells(i, 1).Value, PSKeyRng, 0)

So I'm not able to use Application.Match reliably on cells that begin
with "C00".

Can someone suggest an alternative to Application.Match or provide a
few pointers on what could be going wrong?

Thanks.

 

[Scott]

Have you tried any of the lookups? (vlookup, etc) Kind of hard to tell what
you're trying to do exactly though, any more details? might help someone
find a better answer. (what about using the excel find function? you can
use a bit of error trapping to catch the times when there is nothing found,
chances are this would be the fastest method if you're trying to find the
data)

cheers,

Scott

 

[Dave Peterson]

Have you gotten the equivalent formula to work in a cell?

Maybe there's stuff in one of those cells that makes it so the =match() fails.

Chip Pearson has a very nice addin that will help determine what those
characters are:
http://www.cpearson.com/excel/CellView.htm

You can inspect the two cells that you think should match--just to make sure.

 

[gimme_this_gimme_that]

Hi Everyone,

Thanks for the tips and suggestions.

Here is the code.

Option Explicit
Const AD_EMP_ID_COL = 1

Private Sub TestCompare()
Dim book As Workbook
Set book = ActiveWorkbook
Compare book
End Sub

Sub Compare(book As Workbook)

Application.StatusBar = "Creating Matches/Unmatches"

Dim Match As Long
Dim UnMatch1 As Long
Dim i As Long
Dim n As Long

Dim ad As Worksheet
Dim ps As Worksheet
Dim Matches As Worksheet
Dim unmatches As Worksheet

Dim ADKeyRng As Range
Dim PSKeyRng As Range

Dim res As Variant
Dim myCell As Range

Dim sv As String
Set ad = Worksheets("ActiveDirectory")
Set ps = Worksheets("People")
Set Matches = Worksheets("Matches")
Matches.Cells.Clear
Set unmatches = Worksheets("UnMatches")
unmatches.Cells.Clear

Set PSKeyRng = ps.Range("A1", "A" + Format(RowCount(ps, "A1")))
'Address of PSKeyRng looks something like "A1:A18000"

Match = 1
UnMatch1 = 1
Dim count As Long
count = 1
n = FindLastRowNumber(ad, "samAccountName")
For i = 1 To n
res = Application.Match(Trim(ad.Cells(i, AD_EMP_ID_COL).Value),
PSKeyRng, 0)
'res doesn't return Error for Cells starting with "C000"
'I took out the if statement to use different versions of
Application.Match to keep code easy to read
If IsError(res) Then
ad.rows(i).Copy Destination:=unmatches.rows(UnMatch1)
UnMatch1 = UnMatch1 + 1
Else
ad.rows(i).Copy Destination:=Matches.rows(Match)
Match = Match + 1
End If
Next i
End Sub

Function RowCount(sheet As Worksheet, rname As String) As Long
sheet.Activate
sheet.Range(rname).Select
Range(Selection, Selection.End(xlDown)).Select
If 65536 <> Selection.Cells.count Then
RowCount = Selection.Cells(Selection.Cells.count).row
Else
RowCount = 0
End If
End Function

Function FindLastRowNumber(sheet As Worksheet, colName As String) As
Long
Dim col As Long
col = FindColumnNumber(sheet, colName)
sheet.Activate
sheet.Cells(1, col).Select
Range(Selection, Selection.End(xlDown)).Select
FindLastRowNumber = Selection.rows.count
End Function

 

[Dave Peterson]

The problem could still be in the data.

Hi Everyone,

Thanks for the tips and suggestions.

Here is the code.

Option Explicit
Const AD_EMP_ID_COL = 1

Private Sub TestCompare()
Dim book As Workbook
Set book = ActiveWorkbook
Compare book
End Sub

Sub Compare(book As Workbook)

Application.StatusBar = "Creating Matches/Unmatches"

Dim Match As Long
Dim UnMatch1 As Long
Dim i As Long
Dim n As Long

Dim ad As Worksheet
Dim ps As Worksheet
Dim Matches As Worksheet
Dim unmatches As Worksheet

Dim ADKeyRng As Range
Dim PSKeyRng As Range

Dim res As Variant
Dim myCell As Range

Dim sv As String
Set ad = Worksheets("ActiveDirectory")
Set ps = Worksheets("People")
Set Matches = Worksheets("Matches")
Matches.Cells.Clear
Set unmatches = Worksheets("UnMatches")
unmatches.Cells.Clear

Set PSKeyRng = ps.Range("A1", "A" + Format(RowCount(ps, "A1")))
'Address of PSKeyRng looks something like "A1:A18000"

Match = 1
UnMatch1 = 1
Dim count As Long
count = 1
n = FindLastRowNumber(ad, "samAccountName")
For i = 1 To n
res = Application.Match(Trim(ad.Cells(i, AD_EMP_ID_COL).Value),
PSKeyRng, 0)
'res doesn't return Error for Cells starting with "C000"
'I took out the if statement to use different versions of
Application.Match to keep code easy to read
If IsError(res) Then
ad.rows(i).Copy Destination:=unmatches.rows(UnMatch1)
UnMatch1 = UnMatch1 + 1
Else
ad.rows(i).Copy Destination:=Matches.rows(Match)
Match = Match + 1
End If
Next i
End Sub

Function RowCount(sheet As Worksheet, rname As String) As Long
sheet.Activate
sheet.Range(rname).Select
Range(Selection, Selection.End(xlDown)).Select
If 65536 <> Selection.Cells.count Then
RowCount = Selection.Cells(Selection.Cells.count).row
Else
RowCount = 0
End If
End Function

Function FindLastRowNumber(sheet As Worksheet, colName As String) As
Long
Dim col As Long
col = FindColumnNumber(sheet, colName)
sheet.Activate
sheet.Cells(1, col).Select
Range(Selection, Selection.End(xlDown)).Select
FindLastRowNumber = Selection.rows.count
End Function