Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Gold Lounger
    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...

  2. #2
    Plutonium Lounger
    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>

  3. #3
    Gold Lounger
    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.

  4. #4
    Plutonium Lounger
    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.

  5. #5
    Plutonium Lounger
    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>

  6. #6
    Gold Lounger
    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

  7. #7
    Gold Lounger
    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.

  8. #8
    Plutonium Lounger
    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).

  9. #9
    Gold Lounger
    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?

  10. #10
    Plutonium Lounger
    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?

  11. #11
    Gold Lounger
    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

  12. #12
    Plutonium Lounger
    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?

  13. #13
    Gold Lounger
    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...

  14. #14
    Plutonium Lounger
    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>

  15. #15
    Gold Lounger
    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...?!

Page 1 of 2 12 LastLast

Posting Permissions

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