Solved

Excel sort macro modification

Posted on 2014-04-12
11
570 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
Comment Utility
0
 

Author Comment

by:mfrax
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mfrax
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Closing Comment

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

Expert Comment

by:Ken Butters
Comment Utility
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
Comment Utility
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
Comment Utility
Oh, I was just trying to communicate with Ken Butters. I'll open a question.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now