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

inddon · Oct 10, 2020

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 · Oct 10, 2020

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.

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#D9D9D9, align:CENTER"]Section[/td]
[td="bgcolor:#D9D9D9, align:CENTER"]Decsription[/td]
[td="bgcolor:#D9D9D9, align:CENTER"]Quantity[/td]
[td="bgcolor:#D9D9D9, align:CENTER"]Unit Price[/td]
[td="bgcolor:#D9D9D9, align:CENTER"]Amount[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Paper[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1.5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]15[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Box[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Pen[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3.5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]35[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Pencil[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]40[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Eraser[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6.5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]65[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A Total[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]50[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]175[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Desktop[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]9[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Screen[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]10[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Table[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]15[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]11[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B Total[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]25[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]12[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Chair[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]100[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1.2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]120[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]13[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Glass[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]200[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2.5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]500[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]14[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Cup[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]300[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]45.58[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]13674[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]15[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Book[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]400[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]50.85[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20340[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]16[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C Total[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1000[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]34634[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]17[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Grand Total[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1056[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]34834[/td]
[/tr]
[/table]

inddon · Oct 11, 2020

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 · Oct 11, 2020

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

inddon · Oct 12, 2020

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 · Oct 12, 2020

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 · Oct 12, 2020

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 · Oct 12, 2020

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 · Oct 12, 2020

Hi @vletm

Thank you for your advice.

vletm said:
> 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 · Oct 14, 2020

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 · Oct 15, 2020

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 · Oct 15, 2020

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 · Oct 19, 2020

p45cal said:
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 · Oct 19, 2020

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

inddon · Oct 20, 2020

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

Hello p45cal,

Attached file for your reference.

Regards,
Don

p45cal · Oct 20, 2020

Try replacing that yellow-highlighted line with:

Code:

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

inddon · Oct 20, 2020

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

Regards,
Don

p45cal · Oct 20, 2020

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 · Oct 20, 2020

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 · Oct 20, 2020

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 · Oct 20, 2020

Hi p45cal,

attahed info. snapshot

I am using Windows 10 64 bit.

Regards,
Don

p45cal · Oct 20, 2020

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 · Oct 20, 2020

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 · Oct 20, 2020

p45cal said:
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 · Oct 20, 2020

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.

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

Member

Attachments

Well-Known Member

Member

Excel Ninja

Attachments

Member

Excel Ninja

Member

Excel Ninja

Member

Well-Known Member

Attachments

Member

Well-Known Member

Attachments

Member

Well-Known Member

Member

Attachments

Well-Known Member

Member

Well-Known Member

Attachments

Member

Well-Known Member

Member

Well-Known Member

Well-Known Member

Member

Well-Known Member

Attachments