[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel sort macro modification

Posted on 2014-04-12
11
Medium Priority
?
606 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
8 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 2000 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

612 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