Error 438 and Union function

[eviMogwai]

Hello =)

I am trying to make a function to compare a range with a subset of itself.
Depending on the length of the input, I need to take off a different amount of cells from the ends. Luckily there is only about three possible lengths that can get this far into the function.

So one way I've figured is put the input through a loop, pick the interesting parts and add them together in a new range with the Union function

function foo(rLevels as Range)
'-- rLevels is at least 16 cells
 
'-- code goes here
 
'-- init as Union doesn't handle empty ranges
Set rMyLevels = rLevels(4)
 
    For i = 1 To rLevels.Cells.Count
        If i > 4 And i < 20 Then
           Set rMyLevels = Application.WorksheetFunction.Union(rMyLevels, rLevels.Cells(i))
        End If
        Next

I have deliberately put in 2 different ways of referring to a cell,
rLevels(4) and rLevels.Cells(i), with hope that someone could tell me which one is right, both return the value, but what type is it?

When I run the code (with consistent referencing), i get error '438' Object Doesn't Support This Property Or Method.
Any ideas?


This is a repost of a spin-off of my post at: http://www.mrexcel.com/forum/newreply.php?do=newreply&p=2819179

 

[RoryA]

Union is not a worksheet function.

 

[VoG]

Union isn't a worksheet function.
Try

Set rMyLevels = Union(rMyLevels, rLevels.Cells(i))

 

[eviMogwai]

Thank you, that helped a lot =)