• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loop through Filtered Data (visible rows), to derive calculated value from 2 columns.

inddon

Member
Hello There,

I have 2 worksheets:
Header (Section, Total Quanity, Total Amount)
Lines (Section, Description, Quantity, Unit Price)

I would like to know the following how this can be achieved using VBA code.
1. The common column between 2 worksheets is Column 'Section'
2. For every Header row Section - value, it should get it's related Lines (from worksheet Lines) Totals - Quantity & Amount (Quantity * Unit Price)

I have attached the sampe workbook, with the VBA code, for your reference. The Total Quantiy part is working. However, I could not get the Total Amount working.

Eg.
Header
SectionTotal QuantityTotal Amount
A10
100​
B20
200​
C30
300​


Lines
SectionQuantityUnit Price
A5
10​
A5
10​
B20
100​
C1020
C205

Thank you for your help.

Regards,
Don
 

Attachments

  • Sample workbook.xlsm
    25.9 KB · Views: 7
Just a thought. Instead of writing code, why not just use the table on Sheet Lines and put a Amount column as shown below and employ the subtotal function native to Excel.

vABCDE
1SectionDecsriptionQuantityUnit PriceAmount
2APaper101.515
3ABox10220
4APen103.535
5APencil10440
6AEraser106.565
7A Total50175
8BDesktop188
9BScreen212
10BTable3515
11B Total625
12CChair1001.2120
13CGlass2002.5500
14CCup30045.5813674
15CBook40050.8520340
16C Total100034634
17Grand Total105634834
 
Hi @AlanSidman

Thank you for your reply and your suggestion. That was also my first thought. However, the user does not want any changes in the worksheet design as this will have an impact on other processes.

Therefore, this requirement do it via VBA.

Look forward to hearing from you.

Regards,
Don
 
inddon
There seems to be some ActiveX-component - not work for me.
... but ... something like this? ... press Your button.
 

Attachments

  • Sample workbook.xlsb
    23.5 KB · Views: 3
Hi @vletm

Thank you for taking the time to look into my request and to provide the solution. Appreciate your help.

The challenge for me is the following. In the actual design there are additinal columns in the Header section and it's other processes are based on all filtered data. Implementing your part of the code in the actual existing structure will be a bit complex.

I was looking for how we can loop and read (and calculate) the visible row/column values of a filtered data? The vba code sample workbook in my earlier post for reference.

Many thanks and look forward to hearing from you.

Regards,
Don
 
inddon
If Your samples are different than Your real data then ... I can wonder why did You send that kind of sample?
My sample code 'work' only with visible rows.
I checked Your reference code .. and I used it as much I could.
What is missing?
 
Thank for your reply @vletm

I agree, your code works perfect on the visible rows in the worksheet Lines (i.e. unfiltered data)

The purpose of attaching my sample workbook was to keep the requirement simple, and to know exactly how one can loop and only read values of the visible rows in a filtered data.

Example (in my VBA Code sample):
-Loop Worksheet 'Header' (eg. Row 1 Column Section = 'A')
- Filter Rows in Worksheet Lines where Lines (Column Section 'A') = Header (Column Section 'A')
- Sum quantity of Lines for Section = 'A' (already filtered data) and display in worksheet Header Column Total. This is working
- Loop Worksheet 'Lines' for Section = 'A' (already filtered data):
- Calculate and sum Unit Price * Quanity for Column Section = 'A'
- End loop
- Display the Lines Sum Unit Price * Quantity in worksheet 'Header' Row 1 Section 'A'
-End loop

The exact structure of looping through the visible filtered data and reading the values is not working for me ( i.e. Quantity * Unit price). This is the piece of functionality I would like to know how to get it working.

Hope I was able to explain it better.

Regards,
Don
 
inddon
> What would be simple?
> how one can loop and only read values of the visible rows in a filtered data.
... as in my code - if rowheight is over zero
Why do You want to filter?
... or of course, if You would copy filtered rows somewhere and then You could use only those row ... but that needs more code.
>
This is the piece of functionality I would like to know how to get it working.
Use my code.
or
Solve again needed steps = how would You do it with pencil and paper?
... my opinion, that You won't do as You've written.
 
Hi @vletm

Thank you for your advice.


> What would be simple?
> how one can loop and only read values of the visible rows in a filtered data.
... as in my code - if rowheight is over zero
I learned something new today, thank you

Why do You want to filter?
... or of course, if You would copy filtered rows somewhere and then You could use only those row ... but that needs more code.
>
This is the piece of functionality I would like to know how to get it working.
Use my code.
You are right. I will further debug and analyse your code and put it to best use. :)


Regards,
Don
 
In the attached, filter the table on the Lines sheet as you will, go back to the Headers sheet and click Button 1. The first time it'll take a few seconds to update, subsequent updates will be very quick.
 

Attachments

  • Chandoo45152Sample workbook.xlsm
    32.5 KB · Views: 3
Thank you p45cal for taking the time to provide the solution.

I am getting the below error message on button click

Also, on Worksheet Header, the Sub GetTotals is missing. Could you upload the latest version of your file?

71643

Regards,
Don
 
The whole idea of what I offered was to be a replacement for the GetTotals sub.
Also I hadn't realised you wanted a plain summary of the unfiltered table on the Lines sheet, which makes it simpler.
See attached.
You are using Excel 2016 aren't you?
 

Attachments

  • Chandoo45152Sample workbook.xlsm
    30.8 KB · Views: 4
The whole idea of what I offered was to be a replacement for the GetTotals sub.
Also I hadn't realised you wanted a plain summary of the unfiltered table on the Lines sheet, which makes it simpler.
See attached.
You are using Excel 2016 aren't you?

Hi p45cal,

I am still getting the same error message (as on previous post) on button click. I am using Excel 2016.

Regards,
Don
 
Try replacing that yellow-highlighted line with:
Code:
    ThisWorkbook.Connections("Query - Table1").Refresh
 
Try replacing that yellow-highlighted line with:
Code:
    ThisWorkbook.Connections("Query - Table1").Refresh

Hi p45cal,

I replaced the line with the new one and getting the below message 9On button press and also on F8):


71685

71684


Regards,
Don
 
That is not the macro in the file I attached to msg#12.
When I discovered that you wanted something very simple I abandoned the idea of the hidden sheet SourceData.
I attach that file again but with the modified blah nacro.
I've manually deleted the contents of cell C4 so that you can see the value appear on clicking the button.
 

Attachments

  • Chandoo45152Sample workbookB.xlsm
    30.3 KB · Views: 2
Hi p45cal,

I downloaded the post attached file and also the one you attached the last one, both the code in the sub blah gives an error. I changed the code to the new statement in the lastest file you attached and it gives the same error as before.

Below 2 snapshots. Are these using PowerQuery? If you can tell me PQ steps, I can follow the same and do it manually.


71687


71688


Thanks & ergards,
Don
 
1. Are you on a Mac?
2. Can you give a screenshot of the popup which appears when you click the About Excel button in the Account section of the File menu:
71689

Only include the top line to maintain your privacy; something like:
71690
 
And when the error about 'arguments passed to a function' pops up does it highlight a line in the vba code (having chosen 'Debug')?
 
Yes, it is Power Query. If you right-click on the table which is supposed to give you the results, and choose Table, > Edit Query…
it should take you direct to the Power Query editor. Try selecting each step in turn and see if/when it returns an error - I'm beginning to think it is the
PromoteHeaders line because it's the only function which has 2 arguments, perhaps you have an old version of Power Query and it could do with updating, in the meantime, try changing:
= Table.PromoteHeaders(Source, [PromoteAllScalars=true])
to:
= Table.PromoteHeaders(Source)

I'm almost certain this will work.
See: https://social.technet.microsoft.co...654ccaf67d/power-query-versions-in-excel-2016
 
Last edited:
Yes, it is Power Query. If you right-click on the table which is supposed to give you the results, and choose Table, > Edit Query…
it should take you direct to the Power Query editor. Try selecting each step in turn and see if/when it returns an error - I'm beginning to think it is the
PromoteHeaders line because it's the only function which has 2 arguments, perhaps you have an old version of Power Query and it could do with updating, in the meantime, try changing:
= Table.PromoteHeaders(Source, [PromoteAllScalars=true])
to:
= Table.PromoteHeaders(Source)

I'm almost certain this will work.


it highlights the below line on button press (Debug):
71694

and when the highlihted line is changed to and button pressed:

71696
it gives this error message:

71697


Regards,
Don
 
The macro with .UsedRange.Name = "SceData" is the old macro and it does something different.
I attach file with my PromoteHeaders amendment to cater for older Power Query versions.
 

Attachments

  • Chandoo45152Sample workbookC.xlsm
    31 KB · Views: 1
Back
Top