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

[inddon]

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

Section	Total Quantity	Total Amount
A	10	100​
B	20	200​
C	30	300​

Lines

Section	Quantity	Unit Price
A	5	10​
A	5	10​
B	20	100​
C	10	20
C	20	5

Thank you for your help.

Regards,
Don

 

[AlanSidman]

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.

v	A	B	C	D	E
1	Section	Decsription	Quantity	Unit Price	Amount
2	A	Paper	10	1.5	15
3	A	Box	10	2	20
4	A	Pen	10	3.5	35
5	A	Pencil	10	4	40
6	A	Eraser	10	6.5	65
7	A Total		50		175
8	B	Desktop	1	8	8
9	B	Screen	2	1	2
10	B	Table	3	5	15
11	B Total		6		25
12	C	Chair	100	1.2	120
13	C	Glass	200	2.5	500
14	C	Cup	300	45.58	13674
15	C	Book	400	50.85	20340
16	C Total		1000		34634
17	Grand Total		1056		34834

 

[inddon]

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

 

[vletm]

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

 

[inddon]

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

 

[vletm]

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?

 

[inddon]

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

 

[vletm]

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.

 

[inddon]

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

 

[p45cal]

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.

 

[inddon]

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?



Regards,
Don

 

[p45cal]

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?

 

[inddon]

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

 

[p45cal]

OK, attach your file here, the one giving you the error.

 

[inddon]

OK, attach your file here, the one giving you the error.

Hello p45cal,

Attached file for your reference.

Regards,
Don

 

[p45cal]

Try replacing that yellow-highlighted line with:

    ThisWorkbook.Connections("Query - Table1").Refresh

 

[inddon]

Try replacing that yellow-highlighted line with:

    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):







Regards,
Don

 

[p45cal]

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.

 

[inddon]

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.








Thanks & ergards,
Don

 

[p45cal]

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:


Only include the top line to maintain your privacy; something like:

 

[inddon]

Hi p45cal,


attahed info. snapshot

I am using Windows 10 64 bit.




Regards,
Don

 

[p45cal]

And when the error about 'arguments passed to a function' pops up does it highlight a line in the vba code (having chosen 'Debug')?

 

[p45cal]

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

 

[inddon]

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):


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


it gives this error message:




Regards,
Don

 

[p45cal]

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.