CODEWITHSUNDEEP
excelvba
warsong
warsong

Reputation: 1038

How do I compare two ranges in Excel where some values are integers but others are strings?

I am trying to compare one range against another but I'm having problems because one of them has integer values and these don't seem to work correctly with the Application.Match function (or indeed the Application.WorkSheetFunction.Match function).

For example, one of the values (2616949) in ExampleRange1 which shouldn't be a match returns a value of 5 (I'm aware that the value being returned is the value's position in the range that is being compared against) even though 2616949 is not in ExampleRange2. But other values like D248330 which do exist in both ranges are matching with the correct position.

My theory is that because of the 'On Error Resume Next', the value in dnmatch is not being overwritten when it comes across an integer, but I can't prove this. Could I be right?

Dim dnmatch As Integer
Dim ExampleRange1 As Range, ExampleRange2 As Range

Set ExampleRange1 = Ws2.Range("G1:G" & count1) 
Set ExampleRange2 = Ws3.Range("A2:A" & count2) 

For Each dn In ExampleRange1 

On Error Resume Next
    dnmatch = Application.WorksheetFunction.Match(dn, ExampleRange2, 0)   

    If dnmatch < 1 Then

        Debug.Print ("no match found")

    End If

Next dn

Upvotes: 0

Views: 50

Answers (3)

Sebastian B
Sebastian B

Reputation: 451

Match works fine with integers, the error gets thrown when no match is found. Maybe use .find instead

Set rngFound = ExampleRange2.Find(dn.Value, , xlValues, xlWhole)
If rngFound Is Nothing Then
   Debug.Print ("no match found")
Else
   'Found
End If

Upvotes: 1

Pspl
Pspl

Reputation: 1474

You're probably right. Try this:

Dim dnmatch As Integer
Dim ExampleRange1 As Range, ExampleRange2 As Range

Set ExampleRange1 = Ws2.Range("G1:G" & count1) 
Set ExampleRange2 = Ws3.Range("A2:A" & count2) 

For Each dn In ExampleRange1 

On Error GoTo Debugs
dnmatch = Application.WorksheetFunction.Match(dn, ExampleRange2, 0) 
Goto Final
Debugs:
dnmatch = 0
Final:
If dnmatch < 1 Then

    Debug.Print ("no match found")

End If

Next dn

Upvotes: 1

warsong
warsong

Reputation: 1038

Thanks for being my rubber duck as always, SO. I was correct on my hunch so I added 

dnmatch = 0

just after the On Error Resume next to reset the value (which I know I should have done first anyway), but I haven't been doing Excel VBA all that long, so I believe I have an excuse.

Upvotes: 0

Related Questions