Improving Function UDF

[bines53]

Hello friends,

I would like to help, this function does not allow me to enter into other functions, including built-in functions of Excel, should probably supplement the existing code.

Function Pair(r As Range, s As Range) As Integer
  Dim c As Range
  Dim d() As Variant
  ReDim d(1 To s.Cells.Count)
  For i = 1 To s.Cells.Count
  d(i) = s.Cells(i).Value
  Next i

  For Each c In r
  If c <> "" Then
  For i = 1 To s.Cells.Count
  If c = d(i) Then
  Pair = Pair + 1
  d(i) = ""
  GoTo NextC
  End If
  Next i
  End If
NextC:
  Next c
End Function

The function makes a comparison between two tables,

Thank you !

 

[Deepak]

Pls be specific about the query!!!

 

[NARAYANK991]

Hi David ,

I don't think it needs any improvement , unless you can specify the area where you think it needs improvement.

For it to be used as a UDF , all that is required is for it to be placed in a module.

See the attached file.

Narayan

 

[bines53]

Hi Narayan ,

Please enter this link,
http://chandoo.org/forum/threads/an...ta-from-two-sheets-of-different-ranges.24694/

Thank you !

 

[p45cal]

See if the attached does what you want - I can only guess.
I've used the function code from this thread for Pair with a tweak to get rid of the GoTo (which I hate).
In cells H1:H8 is a guess at wat you're looking for; I've moved some ranges onto other sheets. The formulae are not array formulae, they're normally entered formulae and copied down.

 

[bines53]

Hi p45cal,

Thanks for your response, it's not what I'm looking for.

David

 

[p45cal]

LOL