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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,259
Office Version
2013
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,259
Office Version
2013
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,259
Office Version
2013
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,259
Office Version
2013
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,259
Office Version
2013
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).
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top