Solved

Excel sort macro modification

Posted on 2014-04-12
11
590 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
11 Comments
 

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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