i have written some formula which uses a module but when i attempt to drag this formula it wont change?
is there a way of doing this?
formula is as follows
=unique(January!$A$2:$A$198,1)
i need to be able to drag this down so as the 1 changes to 2 and so on..
and the module is
Function UNIQUE(InputRange As Range, ItemNo As Long) As Variant
Dim cl As Range, cUnique As New Collection, cValue As Variant
Application.Volatile
On Error Resume Next
For Each cl In InputRange
If cl.Formula <> "" Then
cUnique.Add cl.Value, CStr(cl.Value)
End If
Next cl
UNIQUE = ""
If ItemNo = 0 Then
UNIQUE = cUnique.Count
Else
If ItemNo <= cUnique.Count Then
UNIQUE = cUnique(ItemNo)
End If
End If
On Error GoTo 0
End Function
Bookmarks