Excel Formula/VBA-Conditional Sum to find contra from multiple entries

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I don't know.
I'm not a code expert and I have not used Solver very much.

There are threads on this board which address similar problems, about having some number of transactions and having to net some of them off against each other.
Try searching for them.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Hi, Miratshah
Question:
1. Your data in column K, are they double type/decimal or just integer?
If they are decimal then I think it's hard to find a solution.

P.s. Rounding off to 1 or 2 is completely allowed
That's what bothers me.


2. If they are integer, how about simplifying the logic, like this:
Using your example in post #1 , we can easily find that for that particular company & job the subtotal in col K is 3000. Using vba, why not just looping in col K until we find some entry which subtotal is 3000, then mark those entries as "WIP" and the rest will be "CONTRA". If we loop from the top then we will mark row 58-59 as WIP or if we loop from the bottom we will mark row 85-87 as WIP.
 

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
Hi, Miratshah
Question:
1. Your data in column K, are they double type/decimal or just integer?
If they are decimal then I think it's hard to find a solution.


That's what bothers me.


2. If they are integer, how about simplifying the logic, like this:
Using your example in post #1 , we can easily find that for that particular company & job the subtotal in col K is 3000. Using vba, why not just looping in col K until we find some entry which subtotal is 3000, then mark those entries as "WIP" and the rest will be "CONTRA". If we loop from the top then we will mark row 58-59 as WIP or if we loop from the bottom we will mark row 85-87 as WIP.
Hi Akuini,

What I can do is, before starting my work, I can convert all decimal to integer by using round off formula. This is not an issue.

I believe point 2 in your post is a wonderful idea. However, can the VBA you are suggesting work in loop for each company code by Job? Please note that amounts will not always be straight forward + or -.

I am now little optimistic that solution is not too far.

Thanks.
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Hi Akuini,
I believe point 2 in your post is a wonderful idea. However, can the VBA you are suggesting work in loop for each company code by Job? Please note that amounts will not always be straight forward + or -.
Thanks.
The Job No is unique for each company, right? I mean 1 company can have 2 or more Job No but 2 companies can’t have the same Job No, correct? For example: job no 1104229 is only for company IN9073
So is it correct to assume that the data grouping for each analysis can be based on Job No only?
 
Last edited:

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
The Job No is unique for each company, right? I mean 1 company can have 2 or more Job No but 2 companies can’t have the same Job No, correct? For example: job no 1104229 is only for company IN9073
So is it correct to assume that the data grouping for each analysis can be based on Job No only?
Yes, It is safe to assume that the data grouping for each analysis can be based on Job number only.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Ok, try this:
I use a mock-up data to test the code.
The code only marks the WPI, the blank means they are CONTRA.
In col M the code add the sub total of each data group.

Code:
[FONT=lucida console][color=Royalblue]Sub[/color] a1086996b()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html[/color][/i]
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color], j [color=Royalblue]As[/color] [color=Royalblue]Long[/color], n [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] x [color=Royalblue]As[/color] [color=Royalblue]Long[/color], k [color=Royalblue]As[/color] [color=Royalblue]Long[/color], z [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] q [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] va, vb, vc

Application.ScreenUpdating = [color=Royalblue]False[/color]
n = Range([color=brown]"C"[/color] & Rows.count).[color=Royalblue]End[/color](xlUp).Row
va = Range([color=brown]"C1:C"[/color] & n)
vb = Range([color=brown]"K1:K"[/color] & n)
[color=Royalblue]ReDim[/color] vc([color=crimson]1[/color] [color=Royalblue]To[/color] n, [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]2[/color])
Range([color=brown]"L1:M"[/color] & n).ClearContents

[color=Royalblue]For[/color] i = [color=crimson]2[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
    j = i:  x = [color=crimson]0[/color]
    
    [color=Royalblue]Do[/color]
        x = x + vb(i, [color=crimson]1[/color])
        i = i + [color=crimson]1[/color]
        [color=Royalblue]If[/color] i > UBound(va, [color=crimson]1[/color]) [color=Royalblue]Then[/color] [color=Royalblue]Exit[/color] [color=Royalblue]Do[/color]
    [color=Royalblue]Loop[/color] [color=Royalblue]While[/color] va(i, [color=crimson]1[/color]) = va(i - [color=crimson]1[/color], [color=crimson]1[/color])
        
    i = i - [color=crimson]1[/color]
    
    vc(i, [color=crimson]2[/color]) = x
    
    [color=Royalblue]If[/color] x = [color=crimson]0[/color] [color=Royalblue]Then[/color] [color=Royalblue]GoTo[/color] [color=Royalblue]skip[/color]:
    
       
    [color=Royalblue]If[/color] x < [color=crimson]0[/color] [color=Royalblue]Then[/color]
        [color=Royalblue]For[/color] k = j [color=Royalblue]To[/color] i
            vb(k, [color=crimson]1[/color]) = vb(k, [color=crimson]1[/color]) * -[color=crimson]1[/color]
        [color=Royalblue]Next[/color]
        x = x * -[color=crimson]1[/color]
    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
    
    
            [color=Royalblue]For[/color] k = j [color=Royalblue]To[/color] i
            
                z = [color=crimson]0[/color]
                
                [color=Royalblue]For[/color] q = j [color=Royalblue]To[/color] k
                z = z + vb(q, [color=crimson]1[/color])
                [color=Royalblue]Next[/color]
                
                Debug.Print z
                    
                    [color=Royalblue]If[/color] z = x [color=Royalblue]Then[/color]
                        vc(k, [color=crimson]1[/color]) = [color=crimson]1[/color]: vc(k, [color=crimson]1[/color]) = [color=brown]"WPI"[/color]: [color=Royalblue]GoTo[/color] [color=Royalblue]skip[/color]:
                    [color=Royalblue]ElseIf[/color] z > x [color=Royalblue]Then[/color]
                        vb(k, [color=crimson]1[/color]) = [color=crimson]0[/color]
                    [color=Royalblue]Else[/color]
                        [color=Royalblue]If[/color] vb(k, [color=crimson]1[/color]) <= [color=crimson]0[/color] [color=Royalblue]Then[/color]
                            vb(k, [color=crimson]1[/color]) = [color=crimson]0[/color]
                        [color=Royalblue]Else[/color]
                            vc(k, [color=crimson]1[/color]) = [color=brown]"WPI"[/color]
                        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
                    [color=Royalblue]End[/color] [color=Royalblue]If[/color]
                    
            [color=Royalblue]Next[/color]
        
[color=Royalblue]skip[/color]:

[color=Royalblue]Next[/color]

Range([color=brown]"L1"[/color]).Resize(UBound(vc, [color=crimson]1[/color]), [color=crimson]2[/color]) = vc
Range([color=brown]"L1"[/color]) = [color=brown]"Manual"[/color]

Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

RESULT:

Excel 2013 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
1
Company No.Company CodeJob No.Account No.Trans. No.Journal No.Date PostedEntry DateDebitsCreditsBalance in BaseManual
2
AA
101​
1​
WPI
3
AA
101​
1​
WPI
4
AA
101​
-3​
5
AA
101​
2​
6
AA
101​
-1​
7
AA
101​
1​
WPI
8
AA
101​
2​
3​
9
AA
102​
-3​
10
AA
102​
1​
11
AA
102​
-1​
12
AA
102​
1​
13
AA
102​
-1​
14
AA
102​
1​
15
AA
102​
2​
0​
16
AD
302​
3​
WPI
17
AD
302​
1​
WPI
18
AD
302​
-1​
19
AD
302​
1​
20
AD
302​
-1​
21
AD
302​
1​
4​
22
AD
501​
2​
23
AD
501​
1​
24
AD
501​
-1​
WPI
25
AD
501​
1​
26
AD
501​
-2​
WPI
27
AD
501​
3​
28
AD
501​
1​
29
AD
501​
-1​
WPI
30
AD
501​
-3​
31
AD
501​
-5​
-4​
Sheet: Sheet1
 

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
So I tried this code in a bigger sample. I am happy to report that it works for the most part however there are places where I found incorrect results. I sorted Job numbers in ascending order so that all job numbers are one after the other, this reduced number of errors for sure. However still there are few places with incorrect results. Column N is where manual contra & WIP are ploted against each transactions and column O is where I am comparing the result if VBA to manual. Sorry for the delay, unfortunately complete HTML was not getting uploaded hence reduced few rows.


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Company No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Company code</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Job No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Account No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Trans. No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Journal No.</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Date Posted</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Entry Date</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Debits</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Credits</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Balance in Base</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Manual</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">SumIFByJob</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">ManualPlotting</td><td style="font-weight: bold;border-right: 1px solid black;border-left: 1px solid black;background-color: #9BC2E6;;">Check</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FF0000;;">8111821</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;">787.00</td><td style="border-top: 1px solid black;;">WIP</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="border-top: 1px solid black;;">CONTRA</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF0000;;">8111821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,021.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF0000;;">8111821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,176.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF0000;;">8111821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,078.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF0000;;">8111821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">9,69,650.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF0000;;">8111821</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-4,062.00</td><td style=";">CONTRA</td><td style="text-align: right;;">9,69,650.00</td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">18,136.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2,07,761.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-18,136.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">18,136.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-1,25,181.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-1,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,25,181.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-1,25,181.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-1,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,25,181.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-1,25,181.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #00FFFF;;">8111876</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-1,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;">1,00,716.00</td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">26,430.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5,915.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6,05,234.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3,08,648.00</td><td style=";">WIP</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1,75,824.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">43,952.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-32,346.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">32,346.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-6,37,580.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6,37,580.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-11,66,005.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">34</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">35</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">11,66,005.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">CONTRA</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-9,97,710.00</td><td style=";">CONTRA</td><td style="text-align: right;;"></td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FF8080;;">8111910</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-3,50,000.00</td><td style=";">CONTRA</td><td style="text-align: right;;">3,68,293.00</td><td style=";">WIP</td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O2</th><td style="text-align:left">=N2=L2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O3</th><td style="text-align:left">=N3=L3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O4</th><td style="text-align:left">=N4=L4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O5</th><td style="text-align:left">=N5=L5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O6</th><td style="text-align:left">=N6=L6</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O7</th><td style="text-align:left">=N7=L7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O8</th><td style="text-align:left">=N8=L8</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O9</th><td style="text-align:left">=N9=L9</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O10</th><td style="text-align:left">=N10=L10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O11</th><td style="text-align:left">=N11=L11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O12</th><td style="text-align:left">=N12=L12</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O13</th><td style="text-align:left">=N13=L13</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O14</th><td style="text-align:left">=N14=L14</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O15</th><td style="text-align:left">=N15=L15</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O16</th><td style="text-align:left">=N16=L16</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O17</th><td style="text-align:left">=N17=L17</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O18</th><td style="text-align:left">=N18=L18</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O19</th><td style="text-align:left">=N19=L19</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O20</th><td style="text-align:left">=N20=L20</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O21</th><td style="text-align:left">=N21=L21</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O22</th><td style="text-align:left">=N22=L22</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O23</th><td style="text-align:left">=N23=L23</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O24</th><td style="text-align:left">=N24=L24</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O25</th><td style="text-align:left">=N25=L25</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O26</th><td style="text-align:left">=N26=L26</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O27</th><td style="text-align:left">=N27=L27</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O28</th><td style="text-align:left">=N28=L28</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O29</th><td style="text-align:left">=N29=L29</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O30</th><td style="text-align:left">=N30=L30</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O31</th><td style="text-align:left">=N31=L31</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O32</th><td style="text-align:left">=N32=L32</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O33</th><td style="text-align:left">=N33=L33</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O34</th><td style="text-align:left">=N34=L34</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O35</th><td style="text-align:left">=N35=L35</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O36</th><td style="text-align:left">=N36=L36</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">O37</th><td style="text-align:left">=N37=L37</td></tr></tbody></table></td></tr></table><br />
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Sorry, after reading your last data I don’t think I can find a good solution for your problem. So maybe someone else here could help.
But I added some lines in the code just to mark the section (with X) where the code can't find the solution, so at least you know where to look, to do it manually.
Here’s the revised code:



Code:
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1086996c()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086996-excel-formula-vba-conditional-sum-find-contra-multiple-entries.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] x [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], z [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] q [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va, vb, vc
[COLOR=Royalblue]Dim[/COLOR] flag [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Boolean[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"C"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"C1:C"[/COLOR] & n)
vb = Range([COLOR=brown]"K1:K"[/COLOR] & n)
[COLOR=Royalblue]ReDim[/COLOR] vc([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] n, [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]2[/COLOR])
Range([COLOR=brown]"L1:M"[/COLOR] & n).ClearContents

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]2[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
    j = i:  x = [COLOR=crimson]0[/COLOR]
    
    [COLOR=Royalblue]Do[/COLOR]
        x = x + vb(i, [COLOR=crimson]1[/COLOR])
        i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
        
    i = i - [COLOR=crimson]1[/COLOR]
    
    vc(i, [COLOR=crimson]2[/COLOR]) = x
    
    [COLOR=Royalblue]If[/COLOR] x = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]GoTo[/COLOR] [COLOR=Royalblue]skip[/COLOR]:
    
    [COLOR=Royalblue]If[/COLOR] x < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
        [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
            vb(k, [COLOR=crimson]1[/COLOR]) = vb(k, [COLOR=crimson]1[/COLOR]) * -[COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]Next[/COLOR]
        x = x * -[COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
            [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
            
                z = [COLOR=crimson]0[/COLOR]
                
                [COLOR=Royalblue]For[/COLOR] q = j [COLOR=Royalblue]To[/COLOR] k
                z = z + vb(q, [COLOR=crimson]1[/COLOR])
                [COLOR=Royalblue]Next[/COLOR]
                
                flag = [COLOR=Royalblue]False[/COLOR]
                
                    [COLOR=Royalblue]If[/COLOR] z = x [COLOR=Royalblue]Then[/COLOR]
                        vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]1[/COLOR]: vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"WPI"[/COLOR]: flag = [COLOR=Royalblue]True[/COLOR]: [COLOR=Royalblue]GoTo[/COLOR] [COLOR=Royalblue]skip[/COLOR]:
                    [COLOR=Royalblue]ElseIf[/COLOR] z > x [COLOR=Royalblue]Then[/COLOR]
                        vb(k, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
                    [COLOR=Royalblue]Else[/COLOR]
                        [COLOR=Royalblue]If[/COLOR] vb(k, [COLOR=crimson]1[/COLOR]) <= [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                            vb(k, [COLOR=crimson]1[/COLOR]) = [COLOR=crimson]0[/COLOR]
                        [COLOR=Royalblue]Else[/COLOR]
                            vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"WPI"[/COLOR]
                        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                    
            [COLOR=Royalblue]Next[/COLOR]
        
[COLOR=Royalblue]skip[/COLOR]:

        [COLOR=Royalblue]If[/COLOR] flag = [COLOR=Royalblue]False[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i
                vc(k, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]"X"[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"L1"[/COLOR]).Resize(UBound(vc, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]2[/COLOR]) = vc
Range([COLOR=brown]"L1"[/COLOR]) = [COLOR=brown]"Manual"[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Last edited:

Miratshah

Board Regular
Joined
Nov 29, 2016
Messages
57
Yes, even that will save lot of time. However the code you just sent marked every transaction as X. Did something change in the code?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
Yes, even that will save lot of time. However the code you just sent marked every transaction as X. Did something change in the code?

Yes, as I said it’s to mark the section (with X) where the code can't find the solution. For the section that the code can find the solution it will mark with WPI ( and blank).
 

Watch MrExcel Video

Forum statistics

Threads
1,102,000
Messages
5,484,113
Members
407,431
Latest member
kalvinswisher

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top