Conditional macro Sum

[LoganTrk]

I need to Check in Sheet 1 the sum of the field “Long” is equal “Long” in Sheet 2 for the same “code” when “Ap” is “0 “”. If this is not correct put in sheet 2 in column “Sum” the value “Not correct”. The problem on this code is that value “Not correct” put on when the “AP” is for example 1 ,but I need only put “not correct” when the longs is not equal and ap is 0 for the same code.


Result code:

Sheet1
[TABLE="width: 420"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Long
[/TD]
[TD]Ap
[/TD]
[/TR]
[TR]
[TD]15-15
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]16-16
[/TD]
[TD]80
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]15-15
[/TD]
[TD]100
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]14-14
[/TD]
[TD]50
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]14-14
[/TD]
[TD]50
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]16-16
[/TD]
[TD]7
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 359"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Long
[/TD]
[TD]
[/TD]
[TD] Validation
[/TD]
[/TR]
[TR]
[TD]15-15
[/TD]
[TD]2350
[/TD]
[TD]
[/TD]
[TD]Not correct
[/TD]
[/TR]
[TR]
[TD]14-14
[/TD]
[TD]100
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16-16
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]Not correct
[/TD]
[/TR]
</tbody>[/TABLE]


Expected result
[TABLE="width: 359"]
<tbody>[TR]
[TD]Code
[/TD]
[TD]Long
[/TD]
[TD]
[/TD]
[TD] Validation
[/TD]
[/TR]
[TR]
[TD]15-15
[/TD]
[TD]2350
[/TD]
[TD]
[/TD]
[TD]Not correct
[/TD]
[/TR]
[TR]
[TD]14-14
[/TD]
[TD]100
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16-16
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Code:

Private Sub SUM2_Click()
 
 
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr1 As Long
Dim lr2 As Long
Set ws1 = Sheets("SUM1")
Set ws2 = Sheets("SUM2")
lr1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
For x = 2 To lr2
    If ws2.Cells(x, "B") <> Application.WorksheetFunction.SumIfs(ws1.Range("B2:B" & lr1), ws1.Range("A2:A" & lr1), ws2.Cells(x, "A")) Then
        ws2.Cells(x, "D") = "Not correct"
    End If
Next x
End Sub

Regards

 

[MickG]

As an alternative this should work for you !!

[COLOR="Navy"]Sub[/COLOR] MG24Jan01
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sum1")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Dn.Offset(, 2).Value = 0 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
       .Add Dn.Value, Dn.Offset(, 1).Value
   [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) + Dn.Offset(, 1).Value
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Sum2")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]If[/COLOR] .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 2).Value = ""
        [COLOR="Navy"]If[/COLOR] Not .Item(Dn.Value) = Dn.Offset(, 1).Value [COLOR="Navy"]Then[/COLOR] Dn.Offset(, 2).Value = "Not Correct"
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[LoganTrk]

WORKS! THANKs! u can explain me please ?

As an alternative this should work for you !!

[COLOR=Navy]Sub[/COLOR] MG24Jan01
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]With[/COLOR] Sheets("Sum1")
    [COLOR=Navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
[COLOR=Navy]If[/COLOR] Dn.Offset(, 2).Value = 0 [COLOR=Navy]Then[/COLOR]
    [COLOR=Navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
       .Add Dn.Value, Dn.Offset(, 1).Value
   [COLOR=Navy]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) + Dn.Offset(, 1).Value
   [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]

[COLOR=Navy]With[/COLOR] Sheets("Sum2")
    [COLOR=Navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]End[/COLOR] With

[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
   [COLOR=Navy]If[/COLOR] .exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        Dn.Offset(, 2).Value = ""
        [COLOR=Navy]If[/COLOR] Not .Item(Dn.Value) = Dn.Offset(, 1).Value [COLOR=Navy]Then[/COLOR] Dn.Offset(, 2).Value = "Not Correct"
  [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

You're welcome

To understand code, you will first need to understand "Dictionaries"
Have a look here:-
https://analystcave.com/excel-vba-dictionary-arrays-and-other-data-structures/