Unique Count Values by Advanced Filtering based upon UForm CBox Selection

[Southfish]

Hello,

I’m working in Excel 2007 and I’m having a problem with advanced filter, copy and count unique records. The attached file has a table report (“ExpComInvc”), userform (to set up invoice), table (“ExpData”) and temp tbl (“CIDataFltrd”) where unique values (from Col S of ExpData tbl) are copied (macro CIPkgListUnique) in order to populate a combobox (Col A of CIDataFltrd tbl) upon initialization of userform, which appears to work fine although the code for clearing Col A before the advanced filter runs may need to be optimized. An advanced filter macro then is supposed to copy the filtered values (from Col D of ExpData tbl) to the CIDataFltrd tbl (col B) based upon the value selected from the combobox (macro CIBoxFilter). A VBA function (macro CICntBoxes ) then counts the values in the temp table (Col B of CIDataFltrd tble) to get the unique records count which is the number of boxes. The unique value count (# of boxes) appears on the userform (at this time for test purposes) and then is written to a field in the table report. The issue is that the advanced filter copies more values than it should (for example it should copy 3 values but also includes values that are outside of the value chosen from the combobox) therefore the unique record count is off.

Another issue is that I am trying to total weights (macros CI***WgtSum***) based upon the combobox selection and grade (cols B and G of ExpData tbl).

I’m trying to execute the above by clicking the review invoice button on the invoice which may or may not be possible or the best way to approach the desired functionality.

I’ve spent considerable time trying to resolve these two similar issues without success. I would really appreciate any help in solving this issue.
Thanks much!

[Charles]

Hi,

What are the criteria's. What is the column/columns you want to be set for it.

[Norie]

How can we recreate the problem?

I've been playing with the from, and looking at the code but I'm kind of lost.

[Southfish]

To clarify...

If I choose packing list no 1009-12 from the combobox (packing list no) on the form, 3 should be returned as row/box count, for 1015-12, 3 should be returned, and for 1021-12, 2 should be returned. Currently it appears that it returns the max row (box count) for any selection, ignoring the criteria based upon the picklist choice (packing list no) from the userform. Does this clarify? Please let me know.

[Southfish]

Sorry,

Macro (CIPkgListUnique) is used to populate combobox (CIPkgListNo) on the user form from Col S of ExpData tbl (copied to Col A of CIDataFltrd via macro "CIPkgListUnique"). When you make a selection in the combobox, values are copied to Col B of tbl CIDataFltrd via macro (CIBoxFilter) and maco (CICntBoxes) does the row count based on the values written to Col B of tbl CIDataFltrd.

[Norie]

Still not following.

What exactly should I be looking at and doing to get show the problem?

I've tried selecting various items from the Packing List No but I don't know how to connect that value to the rest of the data to get the box count.

[Southfish]

Open the form, Col A of CIDataFiltrd is autopopulated, choose 1009-12 from the packing list no. combobox, then click on view invoice which runs macro to filter only those boxes that correspond to 1009-12 which are 1,2,3. If you then close form and look in Col B of CIDataFiltrd, you will see 1,2,3,4 which is the max. of the unique values based on all of Col D on ExpData sheet.

[Southfish]

It's not filtering Col D of sheet "ExpData" based on the "specific" Packing List No. selected on the combobox. A value of 4, or four rows is returned no matter which packing list no. is selected. I hope the data in the rest of the columns in "ExpData" sheet is not causing confusion, but they are not necessary for calculating no. of boxes. Perhaps I should have deleted all columns with the exception of Cols D and S of "ExpData" sheet which is only data required.

[Norie]

You haven't specified any criteria in your advanced filter.

Also, why are you filtering column D which contains BoxNo?

If you wanted to filter on the value from the combobox shouldn't it at least be column B, PkgListNo, and perhaps some more columns?

[Southfish]

I'm confused...

This macro (populateCIPkgListNo) copies the unique values from col S to col A (CIDataFltrd)

Please Login or Register  to view this content.

which populates the combobox packing list no.

Then, this macro (CIFltrBox) copies the values from col D for box numbers to col B of sheet (CIDataFltrd)

Please Login or Register  to view this content.

So my error must be that I may be skipping the filtering of Col A of sheet (CIDataFltrd) before it extracts only the box numbers which match the packing list number selected from the combobox. I have tried unsucessfully to use userform2.CIPkgListNo.value as the criteriarange:=, but I get the error method range failed.

I believe you are onto something, although, I didn't think col B of sheet (ExpData) would be used as the unique values had already been copied to col A of sheet (CIDataFltrd) and I thought that the latter should be referenced in the advanced filter

[Southfish]

Based upon your comments I Modified the Macro CIFltrBox which seems to get me closer as now col A of sheet CIDataFiltrd, the packing list number now is filtered but the problem is still in the advanced filter code.

Please Login or Register  to view this content.

Like I said, I'm confused as I have very little experience with the advancedfilter method

Please copy and run the modifed macro if you have time.

Thanks for your patience!

[Southfish]

Norie,

I added a criteria range to the macro but it still does not work as it should. It now gives the unique box numbers for eack of the combobox (packing list number) selections as opposed to the box numbers for the specific packing list number selected from the combobox.

I've attached the file to show the reurned results on sheet "CIDataFltrd".

I'd be grateful for any further assistance you can provide.

Thanks

[Norie]

The criteria range needs to include field names as well as the values you want to filter with.

[Southfish]

Thanks so much Norie for steering me in the right direction. UniqFltrTestSucess.xlsm