Macro to apply filter, use if statement to generate subtotal, return value

Hello,

My workbook has two sheets: Input (large bank of data) and Output (simplified presentation sheet)

I need a macro that will perform in the following order:

(1)sort the Input sheet by column A
(2)On the Input sheet run an if statement on column K and L where --> If (absolute(Column K)) - (Absolute(Column L)) > 100, then the value of ($K - $L) is added to a running subtotal
(3)return value of the summed subtotal to Output sheet


My main problem comes with form the If statement to run a subtotal.

Many thanks in advance Excel Forum.

Try something like this. The total is put in sheet Output range A1.
Assumed there is a header in row 1 of sheet Input.

Please Login or Register  to view this content.

Originally Posted by VBA_Newguy

Hi AlphaFrog,

that sub seems to be having a problem when it comes to returning the value to the Output page when i run it.

although your code did help me catch something that i should have mentioned:
Rather than send the entire formula to the output page (Cell:A1), i only want the subtotal value to be sent to A1.

i assume that means it should be stored as a variable somewhere?

Can you attach an example workbook? Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Not sure how you are defining LastCell.

I think this is what you want for absolute difference.

Please Login or Register  to view this content.

LastCell is defined as:

LastCell = Sheets(OOTab).Range("K1").End(xlDown).Row


ill give that a try and report back, thank you.



Edit:

The sub still runs, but, once again, the values in column P are not populating.
Strange.

That's all I got based on the limited information provided.

If you want to attach a sample workbook, I'll take a look. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.