Dear Mike,
Sorry to bother you again but the code is giving wrong results. I will give
you detailed information so that you can find out the problem.
See the list of Categories is in Sheet2(Code Name of Sheet) and the Range of
Categories start from B1, B1 is the Header and the data continues from B2.
The List of Tasks is in column C starting from C1 which is the Header and the
Task data continues from Cell C2. Can you please advice what is wrong with
the code given by you. I have placed the code in the Userform Initialize
event.
I am awaiting for an early response as it is very urgent.
Thanks a ton in advance..... please I request you to help me get out of this
problem...please
--
Best Regards,
Jitendra Kumar
"Jitendra Kumar" wrote:
> Dear Mike,
>
> Thanks a lot for the solution provided by you. It is working.
> I readlly appreciate the way you responded with such a good answer. It is
> exactly what was required.
>
> Thanks a lot once again from the bottom of my heart. :-)
>
> --
> Best Regards,
> Jitendra Kumar
>
>
> "Mike" wrote:
>
> > This may work for you. I found it on somewhere
> > on the web and tweek it for you
> > Option Explicit
> > Private Sub UserForm_Initialize()
> > Dim rngCatagory As Range, Cell As Range
> > Dim NoDupes As New Collection
> > Dim i As Integer, j As Integer
> > Dim Swap1, Swap2, Item, lastRow
> >
> > ' The items are in A1:A to lastrow in column a
> > lastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > Set rngCatagory = Range("A1:A" & lastRow)
> >
> > ' The next statement ignores the error caused
> > ' by attempting to add a duplicate key to the collection.
> > ' The duplicate is not added - which is just what we want!
> > On Error Resume Next
> > For Each Cell In rngCatagory
> > NoDupes.Add Cell.Value, CStr(Cell.Value)
> > ' Note: the 2nd argument (key) for the Add method must be a string
> > Next Cell
> >
> > ' Resume normal error handling
> > On Error GoTo 0
> >
> > ' Sort the collection (optional)
> > For i = 1 To NoDupes.Count - 1
> > For j = i + 1 To NoDupes.Count
> > If NoDupes(i) > NoDupes(j) Then
> > Swap1 = NoDupes(i)
> > Swap2 = NoDupes(j)
> > NoDupes.Add Swap1, before:=j
> > NoDupes.Add Swap2, before:=i
> > NoDupes.Remove i + 1
> > NoDupes.Remove j + 1
> > End If
> > Next j
> > Next i
> >
> > ' Add the sorted, non-duplicated items to a ListBox
> > For Each Item In NoDupes
> > UserForm1.ComboBox1.AddItem Item
> > Next Item
> > End Sub
> > Private Sub ComboBox1_Change()
> > Dim rngCatagory As Range, Cell As Range
> > Dim i As Integer, lastRow
> >
> > lastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > Set rngCatagory = Range("A1:A" & lastRow)
> >
> > ComboBox2.Clear
> > For Each Cell In rngCatagory
> > If Cell.Value = ComboBox1.Value Then
> > ComboBox2.AddItem (Cell.Offset(0, 1).Value)
> > End If
> > Next Cell
> > End Sub
> >
> > "Jitendra Kumar" wrote:
> >
> > > Dear Excel Experts,
> > >
> > > Please help me in getting this done because its very urgently required.. It
> > > will be a great help for me... please
> > > I have two columns of data, Column A contains Category and Column B contains
> > > Task. There are repetitive entries in both the columns. please see the sample
> > > data below:
> > >
> > > Category Task
> > > Networking Conduiting - UTP
> > > Networking Laying - UTP
> > > Networking Installation - UTP
> > > Electrical Dedicated Earth
> > > Electrical Ess / Non ess Power
> > > Electrical Conduiting / Laying
> > >
> > > What I want to do is, I want to have two combo boxes on a user form with the
> > > names "Category" & "Task" and the "Category" Combo Box should show the
> > > options after extracting the unique entries from column A i.e. Category
> > > column and the "Task" Combo Box should show the Unique entries in Column B
> > > i.e. Task Column. Further more I want that If I select "Electrical" as
> > > Category then the Task Combo Box should show the options available with
> > > "Electrical" Category only and not the options of other Categories like
> > > "Networking".
> > >
> > > I hope that I was able to explain what I need and I am seriously looking
> > > forward for a good and useful answer from the experts of this group....
> > >
> > > Many Thanks in advance,
> > > Jitendra
> > >
> > >
|
Similar Threads
