VBA - running scripting dictionary (error!)
Hi,
Following your advice, I am trying this code, in order to show item listed in combobox only once each item. (like autofilter), but i doesn't work, (it gives mi error: Variable not defined and remarkin "Scripting") Private Sub UserForm_Initialize() 'Set a reference to MS Scripting runtime Set Dic = CreateObject(Scripting.Dictionary) Dim Dic As New Scripting.Dictionary Dim rSource As Range, cell As Range Dim sVal As String Dim MyList As String 'Point to specific column MyList = "P3:P20" Set rSource = Range("MyList") With ChapExpCB For Each cell In rSource.Cells sVal = cell.Value If Not Dic.Exists(sVal) Then Dic.Add sVal, sVal .AddItem sVal End If Next End With Set Dic = Nothing End Sub Thanks in advance -- Message posted from http://www.ExcelForum.com |
VBA - running scripting dictionary (error!)
You need quotes around the "Scripting.Dictionary" in the
CreateObject function. E.g., Set Dic = CreateObject("Scripting.Dictionary") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ajliaks " wrote in message ... Hi, Following your advice, I am trying this code, in order to show items listed in combobox only once each item. (like autofilter), but it doesn't work, (it gives mi error: Variable not defined and remarking "Scripting") Private Sub UserForm_Initialize() 'Set a reference to MS Scripting runtime Set Dic = CreateObject(Scripting.Dictionary) Dim Dic As New Scripting.Dictionary Dim rSource As Range, cell As Range Dim sVal As String Dim MyList As String 'Point to specific column MyList = "P3:P20" Set rSource = Range("MyList") With ChapExpCB For Each cell In rSource.Cells sVal = cell.Value If Not Dic.Exists(sVal) Then Dic.Add sVal, sVal AddItem sVal End If Next End With Set Dic = Nothing End Sub Thanks in advance. --- Message posted from http://www.ExcelForum.com/ |
VBA - running scripting dictionary (error!)
You didn't follow my advice. My advice showed
Set Dic = CreateObject("Scripting.Dictionary") You wrote Set Dic = CreateObject(Scripting.Dictionary) also Dim Dic As New Scripting.Dictionary shouldn't be used if you use late binding. You would use that for early binding (creating a reference) and would not use the createobject line. Dim dic as Object for late binding -- Regards, Tom Ogilvy "ajliaks " wrote in message ... Hi, Following your advice, I am trying this code, in order to show items listed in combobox only once each item. (like autofilter), but it doesn't work, (it gives mi error: Variable not defined and remarking "Scripting") Private Sub UserForm_Initialize() 'Set a reference to MS Scripting runtime Set Dic = CreateObject(Scripting.Dictionary) Dim Dic As New Scripting.Dictionary Dim rSource As Range, cell As Range Dim sVal As String Dim MyList As String 'Point to specific column MyList = "P3:P20" Set rSource = Range("MyList") With ChapExpCB For Each cell In rSource.Cells sVal = cell.Value If Not Dic.Exists(sVal) Then Dic.Add sVal, sVal AddItem sVal End If Next End With Set Dic = Nothing End Sub Thanks in advance. --- Message posted from http://www.ExcelForum.com/ |
VBA - running scripting dictionary (error!)
shouldn't scripting.dictionary be a string: Set Dic = CreateObject("Scripting.Dictionary"
----- ajliaks wrote: ---- Hi Following your advice, I am trying this code, in order to show item listed in combobox only once each item. (like autofilter), but i doesn't work, (it gives mi error: Variable not defined and remarkin "Scripting" Private Sub UserForm_Initialize( 'Set a reference to MS Scripting runtim Set Dic = CreateObject(Scripting.Dictionary Dim Dic As New Scripting.Dictionar Dim rSource As Range, cell As Rang Dim sVal As Strin Dim MyList As Strin 'Point to specific colum MyList = "P3:P20 Set rSource = Range("MyList" With ChapExpC For Each cell In rSource.Cell sVal = cell.Valu If Not Dic.Exists(sVal) The Dic.Add sVal, sVa .AddItem sVa End I Nex End Wit Set Dic = Nothin End Su Thanks in advance -- Message posted from http://www.ExcelForum.com |
VBA - running scripting dictionary (error!)
As a side note...
MyList = "P3:P20" Set rSource = Range("MyList") You may get an error here. If so, see if this will work without the quotes. MyList = "P3:P20" Set rSource = Range(MyList) If Not Dic.Exists(sVal) Then Usually, it is not necessary to test for Existence first. I find it easier to use an "On error" as in this simple example... On Error Resume Next For Each cell In rSource.Cells sVal = cell.Value Dic.Add sVal, sVal Next Just some ideas. HTH. :) -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "ajliaks " wrote in message ... Hi, Following your advice, I am trying this code, in order to show items listed in combobox only once each item. (like autofilter), but it doesn't work, (it gives mi error: Variable not defined and remarking "Scripting") Private Sub UserForm_Initialize() 'Set a reference to MS Scripting runtime Set Dic = CreateObject(Scripting.Dictionary) Dim Dic As New Scripting.Dictionary Dim rSource As Range, cell As Range Dim sVal As String Dim MyList As String 'Point to specific column MyList = "P3:P20" Set rSource = Range("MyList") With ChapExpCB For Each cell In rSource.Cells sVal = cell.Value If Not Dic.Exists(sVal) Then Dic.Add sVal, sVal AddItem sVal End If Next End With Set Dic = Nothing End Sub Thanks in advance. --- Message posted from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 11:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com