Results 1 to 15 of 26

20081104, 03:36 #1
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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...

20081104, 04:05 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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>

20081104, 05:28 #3
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20081104, 05:35 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: LOOP for maching if algebric summ is = 0 (2000)
I'll have to look at that later on.

20081104, 05:47 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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>

20081104, 05:51 #6
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: LOOP for maching if algebric summ is = 0 (2000)
RESOLVED!!!!
TKS!
Brilliant code...
last your message resolved is 75877 now 75877 + 1 =75878

20081104, 08:15 #7
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?
admit, to explain:
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.

20081104, 08:37 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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).

20081104, 08:42 #9
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20081104, 08:47 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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?

20081104, 09:43 #11
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20081104, 09:47 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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?

20081104, 09:59 #13
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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...

20081104, 10:02 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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>

20081104, 10:06 #15
 Join Date
 Jan 2004
 Location
 Italy
 Posts
 3,245
 Thanks
 0
 Thanked 0 Times in 0 Posts
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...?!