# Thread: LOOP for maching if algebric summ is = 0 (2000)

1. ## LOOP for maching if algebric summ is = 0 (2000)

In visible cells in column I is possible to match a pair of cell and is the algebric summ is=0 the in column Z insert ZERO for the pair of cell matched... Maked a code but really not work.
Naturally not consider the matched cells with value ZERO in column Z, are already tested...

2. ## Re: LOOP for maching if algebric summ is = 0 (2000)

Does this do what you want?
<code>
Sub CheckPairs()
Dim r As Long
Dim s As Long
' Loop through rows 2 to 223
For r = 2 To 223
' Skip rows that already have ZERO in column Z
If Not Cells(r, 26) = "ZERO" Then
' Loop through the rows below
For s = r + 1 To 223
' Again, skip rows with ZERO in column Z
If Not Cells(s, 26) = "ZERO" Then
' Check whether cell pair in column I adds to 0
If Cells(r, 9) + Cells(s, 9) = 0 Then
' If so, enter ZERO in column Z
Cells(r, 26) = "ZERO"
Cells(s, 26) = "ZERO"
' And exit the inner loop
Exit For
End If
End If
Next s
End If
Next r
End Sub</code>

3. ## Re: LOOP for maching if algebric summ is = 0 (2000)

I'm, here...
tested the code and work fine....
Only a dubt my filter of cells are dinamic and it chenaged from other macro....
Is possible to loop only visible cells from the first visible and the last visible.
Tks for patience.

4. ## Re: LOOP for maching if algebric summ is = 0 (2000)

I'll have to look at that later on.

5. ## Re: LOOP for maching if algebric summ is = 0 (2000)

That turned out to be easier than I thought. Try this:
<code>
Sub CheckPairs()
Dim r As Long
Dim s As Long
' Loop through rows 2 to 223
For r = 2 To 223
' Skip rows that already have ZERO in column Z or are hidden
If Not Cells(r, 26) = "ZERO" And Not Rows®.Hidden Then
' Loop through the rows below
For s = r + 1 To 223
' Again, skip rows with ZERO in column Z or are hidden
If Not Cells(s, 26) = "ZERO" And Not Rows(s).Hidden Then
' Check whether cell pair in column I adds to 0
If Cells(r, 9) + Cells(s, 9) = 0 Then
' If so, enter ZERO in column Z
Cells(r, 26) = "ZERO"
Cells(s, 26) = "ZERO"
' And exit the inner loop
Exit For
End If
End If
Next s
End If
Next r
End Sub</code>

6. ## Re: LOOP for maching if algebric summ is = 0 (2000)

RESOLVED!!!!
TKS!
Brilliant code...
last your message resolved is 75877 now 75877 + 1 =75878

7. ## Re: LOOP for maching if algebric summ is = 0 (2000)

Hi Hans... to understand
with the statement .Hidden the cilce for next check the effective visible cells in front?

Afetr i filtre a data in sheet and after i filtre have a range similar:
first blok filtered range I45:range I478, second block filtered I901:range I1478... ecc..
The statement .Hidden cicle the cells effective from I45 to I478 skip the rest an go to I901 to I1478....ecc and not from I45 to I478
Hope you have understand me.

8. ## Re: LOOP for maching if algebric summ is = 0 (2000)

The code skips all cells in hidden rows, so it only compares pairs of cells if both cells are visible (= not hidden).

9. ## Re: LOOP for maching if algebric summ is = 0 (2000)

GREAT!!!! Understand now...
In this case...
Admit i want to cicle in column I all NOT hideen cell, and if the summ is =0 insert in column Z the value "ZERO" in all cells have composed the summ =0...
How tow?

10. ## Re: LOOP for maching if algebric summ is = 0 (2000)

Do you mean that if a cell in column I contains (for example) 3, you want to enter "ZERO" in column Z in *ALL* rows where column I contains -3, instead of just in one row?

11. ## Re: LOOP for maching if algebric summ is = 0 (2000)

Attached real example with summ in column I is =0...
Not important to match a pair of cell in column I but simple if the algebric summ=0

12. ## Re: LOOP for maching if algebric summ is = 0 (2000)

Do you mean that you want "ZERO" in column Z if ALL visible cells in column I add up to 0?

13. ## Re: LOOP for maching if algebric summ is = 0 (2000)

yes!!!!
for example:
-27,00
-3,00
30,00

the algebric sunn is = 0 inset "zero" in Z for each rows...

14. ## Re: LOOP for maching if algebric summ is = 0 (2000)

Try this:
<code>
Sub CheckZeroSum()
Dim m As Long
m = Cells(Rows.Count, 9).End(xlUp).Row
If Application.Sum(Range("I2:I" & m).SpecialCells(xlCellTypeVisible)) = 0 Then
Range("Z2:Z" & m).SpecialCells(xlCellTypeVisible) = "ZERO"
End If
End Sub</code>

15. ## Re: LOOP for maching if algebric summ is = 0 (2000)

THE CODE WOK PERFECT!!!!
But curious... why not have used .hidden statement to chek not hidden cells...?!

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•