Excel sort macro modification

This is the same as the previous question: Excel 2003 sort macro except that in that question all criteria were in sheet on and data in sheet 2. In this question, using the same data, I need to create sheet 3 based on the two previous data in sheet 1 and column N of sheet 2. Attached is the excel workbook with the two sheets.

_______________
Prior related question: http:Q_28252104.html
TestBook1-Result--1-.xls
mfraxAsked:
Who is Participating?
 
Ken ButtersCommented:
I've attached a workbook with updates...

Note... I replaced the macro that the button calls with a userform...

The old code is still in the sheet if you need it, but most of the new code is in the userform1.

Using the form, you will be first given a dropdown list of Possible types... which with current example is Regular or Irregular.

When you answer that, the date dropdown will be populated based on what you selected from the first combobox.

When you select a date from the 2nd combo box, the third combo box will be populated with the media types that are available based on your first two choices.

Then just click on the "Update Rows" button to start the copy.
TestBook1-Result.xls
0
 
mfraxAuthor Commented:
Dear aikimark,

Thanks for taking those actions. I appreciate all of the help and guidance I receive.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mfraxAuthor Commented:
Dear Mr. Butters,
Thank you. you have answered my question. May I ask you a couple of questions?

I did not change the test sheet layout though the actual sheet layout has changed, date is now column two of sheet 1 (instead of column nine). Also added to sheet one is a letter in column for the corresponds to the date, e.g., A, B, C, D etc.  How do I change the userform to seek the letter in sheet 1 column 4 instead of the date?

I don't see how you are able to call up column 14 on sheet 2. In the actual data this is sheet 2 column 29 (AC) and there is unnecessary data beyond that. Can you give me some understanding how this is called up after the first two criteria are selected?
0
 
mfraxAuthor Commented:
Thank you. I was unfamiliar with .frm type macros.
0
 
Ken ButtersCommented:
You awarded points.... so did you figure out the answers to your questions?

As a general overview... what I did was to create a dictionary object.  A dictionary object is an easy way to to keep track of which items I've already added to an array, so that I don't add duplicates.

That was needed so that I didn't add duplicate items to the dropdown combo boxes.   In order to see the code where I populated the "Date" box on the userform, you need to double click on the user form from the VBA Project view.

When the userform is visible right click on the combo-box next to "Select Customer Type"  then select "View Code".

That will take you right to the code that is exectue when the combo box for Customer type is changed.  What I do in that subroutine is to loop through each row in sheet1 and Column 9 and figure out which dates are valid to populate in the Date selection box.

   For i = 2 To myLastCell.Row
        If Me.cboCustomerType = ws.Cells(i, 8) And Not DateDictionary.Exists(ws.Cells(i, 9).Value) Then
            DateDictionary.Add ws.Cells(i, 9).Value, i
            Me.cboDate.AddItem ws.Cells(i, 9).Value
        End If
    Next

Open in new window


first I check to see if the customer type matches, and if it does then I check to see if I've already added that particular date by using the dictionary method "exists".

Not sure if that answers entirely... but let me know if it does not.

At least that should give you an intro of how to see/view/update the code in the userform.

One thing that will help you a lot in the userform... is that the methods available to a control or to the form are available as dropdowns at the top when you are in the code of the userform.   I've attached a screenshot of where the dropdowns are for the available methods so you can see what I mean.
form.jpg
0
 
mfraxAuthor Commented:
Yes, thanks, I answered my questions but one new one has arisen that I can't resolve by inspection. Instead of nine columns I am trying to move 25 columns from sheet2 to sheet3. I changed a sheet2 range in your macro:

If SelectedCustomersDict.Exists(CStr(Wk2.Cells(i, 2).Value)) And Wk2.Cells(i, 29).Value = Me.cboMediaType.Value Then
            wk3.Range("B" & j & ":J" & j).Value = Wk2.Range("D" & i & ":AC" & i).Value j = j + 1

That is to say,  ":L is changed to ":AC . However this has not caused the amount of sheet2 data appearing on sheet3 to increase. What is the instruction that moves nine columns of data in your macro to 25 columns of data?
0
 
mfraxAuthor Commented:
Oh, I was just trying to communicate with Ken Butters. I'll open a question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.