Reputation: 958
I am not new to coding but I am new to Excel and since this is a new platform for me it is difficult for me to understand what the problem is.
I am trying to write my own function but whenever I debug I get the "ding" sound and since there is no error code, just a "ding" sound I have no idea what I am doing wrong. Here is the function.
Function userItems(Range)
Set userObj = CreateObject("Scripting.Dictionary")
For Each Cell In Range
If Not userObj.Exists(Cell.Value) Then
userObj.Add Cell.Value, Cell.Address
End If
Next Cell
userItems = userObj(1)
End Function
Added second Argument
Upvotes: 0
Views: 126
Reputation: 14685
You have several issues.
hope this helps!
Sub test()
Call testFunction(Range("A1:A100"))
End Sub
and the code:
'You need to declare the variable name and type
Sub testFunction(myRange As Range)
'First need to declare the object
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
'Good practice to declare variables, otherwise they are Variants
Dim cell As Range
For Each cell In myRange
If Not dict.Exists(cell.Value) Then
dict.Add cell.Value, cell.Address
End If
Next
'Do something with the result, transpose is good fun
Range("B1").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.keys)
Range("C1").Resize(dict.Count, 1).Value = WorksheetFunction.Transpose(dict.items)
End Sub
I am a huge fan of the dictionary object, but be aware this code will not run on Office for Mac.
Upvotes: 1