Solved

Excel sort macro modification

Posted on 2014-04-12
11
580 Views
Last Modified: 2014-04-15
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
0
Comment
Question by:mfrax
  • 6
  • 2
11 Comments
 

Author Comment

by:mfrax
ID: 39997264
0
 

Author Comment

by:mfrax
ID: 39997702
Dear aikimark,

Thanks for taking those actions. I appreciate all of the help and guidance I receive.
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39999666
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:mfrax
ID: 40000400
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
 

Author Closing Comment

by:mfrax
ID: 40001468
Thank you. I was unfamiliar with .frm type macros.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 40001752
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
 

Author Comment

by:mfrax
ID: 40001898
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
 

Author Comment

by:mfrax
ID: 40002015
Oh, I was just trying to communicate with Ken Butters. I'll open a question.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question