Macro to automate Multiple "IF Formula"

NeilATaylor

Board Regular
Joined
Aug 7, 2007
Messages
185
Good afternoon,

I would really like to see a macro that does the following, I'm sure someone has something already like this, if so, please share!

I have a worksheet of 5 columns. What I would like to do is subtotal an amount in column d, where the item code in column b are the same. Lets say there are 5 rows with code 8150 in column b, I want to add all these rows up BUT ALSO add up any rows which are 8150+1 (x+1) i.e. 8151. All transactions where the code in column B, x + (x+1) = 0. All true results should then be cut and pasted into a new worksheet (one worksheet for all transactions that are true).

e.g.

8150 1233
8150 12
8150 644
8151 -2084
8151 -10
8151 -205

Total of above amounts is zero, therefore cut and paste to new spreadsheet.

Now that I think about it, its actually quite complicated.

Any ideas?

Thanks in advance

Neil
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
First, filter (data --> filter --> autofilter) column B for 8150. Copy and paste the filtered cells to another sheet.

Then, repeat for 8151.

You could use code to automate this; but, even done manually, it should only take you a few seconds...
 
Upvote 0
Thanks for the reply - thats the way I do it at the moment.
The problem is that there might be 100's of "pairs" of transaction codes that clear to Zero, that I need to extract from a sheet for reporting purposes. If a macro could automate this repetative process, it'd save me an hour atleast on my current method. Cheers, Neil.

A better example:
2150 £88
2151 -£88
2133 £102
2138 £60
2139 -£35
2139 -£25

My report would need to match 2150 and 2151 (both clear to zero and code 2151 = 2150 +1) and 2138 and 2139 (both clear to zero etc...)
I might have to repeat this many times on a 10,000 line spreadsheet making me a slave to excel....
 
Upvote 0
Oh, I see. It's fair to say that codes for the offsetting pairs will always differ by 1? (i.e. you wouldn't have 2151 offset 2149?)

In that case, I'd first make a pivot table to sum by code (and sort by ascending code). Then, if A10:A100 are your codes, B10:B100 are the totals for each code, I'd set C10 = AND(A11=A10+1,B11+B10=0) and copy down.

Then, your macro would do the following:

1) Loop through Column C (on the pivot table sheet). Every time it finds a "TRUE", you'll take the code from column A (also on the pivot table sheet).

2) Filter & copy the data sheet for both the code you found from (1) and the code + 1 you found from (1)

Or, if you want, you can make the pivot table and create your column "C" on the pivot table sheet as part of your macro as well...
 
Upvote 0
Thats actually quite ingenius, and simple too! I never would have thought to approach the problem in this way. I guess the final step, and the part that would take me a few years to work out, would be the Macro that does (1) in your answer above. How could I write this to extract all the lines (not the total value of all the lines from the pivot table) from the transaction list to a separate worksheet? Thanks a lot for your help oaktree
 
Upvote 0
Sorry oaktree, could you also help me with the IF/AND formula, because I want to return TRUE if A11=A10+1,B11+B10=0 AND IF A10=A11-1,B11+B10=0?
 
Upvote 0
The AND formula should be what I posted [C10 = AND(A11=A10+1,B11+B10=0)]. You don't need the second test, 'cause you can account for that in the loop (see the two filters below)

You'll have to tweak it a little according to your ranges, but this should get you started:

Code:
Sub create_report()

Dim my_cell As Range, my_code As Integer

    For Each my_cell In Sheets("Pivot Table Sheet").Range("C10:C" _
        & Sheets("pivot table sheet").Range("C65536").End(xlUp).Row)
    
    If my_cell = True Then
        my_code = my_cell.Offset(0, -2)
    
        Sheets("Other Sheet").Range("B1").AutoFilter Field:=1, Criteria1:=my_code
        Sheets("Other Sheet").Range("A2:B10000").SpecialCells(xlCellTypeVisible).Copy _
        Sheets("report sheet").Range("A65536").End(xlUp).Offset(1, 0)
    
        Sheets("Other Sheet").Range("B1").AutoFilter Field:=1, Criteria1:=my_code + 1
        Sheets("Other Sheet").Range("A2:B10000").Copy Sheets("report sheet").Range("A65536").End(xlUp).Offset(1, 0)
    End If
    Next my_cell
End Sub
 
Upvote 0
Using this macro, I get a "run time error 6: Overflow" message.
What does this mean and why is it occuring?
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top