Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula unknown (excel 2000)

    Good Morning! I have a question about a formula. I have insert an attachment if you open that maybe you know what i mean......
    there are three sheets: test, win and loss. when i change something in the test sheet (the loss win column) i want that that row go to the correct sheet. So when it is win that row copy's to the win sheet, and when it is lead nothing must happen........ maybe someone know how i can do this.....
    Thanks Gerwin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula unknown (excel 2000)

    Can a row ever change *after* lead has been changed to win or loss? For example, could win change back to loss, or loss change to win? If so, it becomes rather tricky, for you would have to remove a row from the loss or win worksheet, and it might not be obvious any more which one that should be.

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula unknown (excel 2000)

    No, it only change one time. so if it is lead than it will be loss or win, and that won't change...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula unknown (excel 2000)

    Activate the Visual Basic Editor (Alt+F11)
    Double click Sheet1 (test) in the Project Explorer to open the sheet module.
    Type or copy this worksheet event procedure:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
    If Target.Value = "win" Or Target.Value = "loss" Then
    Application.EnableEvents = False
    Target.EntireRow.Copy _
    Destination:=Worksheets(Target.Value).Range("A6553 6").End(xlUp).Offset(1, 0)
    Application.EnableEvents = True
    End If
    End If
    End Sub

    Test thoroughly on a copy before using it for real!

  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula unknown (excel 2000)

    Thanks!! looks cool....
    maby you can explain the source code so i know what i am doing [img]/forums/images/smilies/biggrin.gif[/img]

    Gerwin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula unknown (excel 2000)

    The Worksheet_Change event occurs whenever the user changes a cell (or cells) in the worksheet. The range of cells being changed is passed in the Target argument.

    The code first tests if the change occurred in column #4, i.e. D.
    If so, it tests if the new value is "win" or "loss".
    If so, event handling is (temporarily) disabled in case you have other worksheet-level or workbook-level events.
    Then, the entire row is copied to the appropriate worksheet Worksheets(Target.Value) - remember, the value is either "win" or "loss" if the code gets here.
    The destination is determined by moving up from the very last cell in column A until a non-blank cell is encountered - Range("A65536").End(xlUp) - then moving one cell down - Offset(1,0).
    Finally, event handling is enabled again.

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula unknown (excel 2000)

    when i put the code in the official document and i insert 2x loss the 3x it overwrite the 2e one.........
    in the test file it all works correct??? do you know what go wrong

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula unknown (excel 2000)

    The macro I wrote assumes that the first cell in every row will be non-blank. Do you have blanks in column A in the rows that are being copied?

  9. #9
    Star Lounger
    Join Date
    Sep 2003
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula unknown (excel 2000)

    yes that's the problem! how can i make it work for the 2e cell, this one have always text.

    Regards gerwin

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula unknown (excel 2000)

    If the cell in column B is always non-blank, change the line that copies and pastes as follows:

    Target.EntireRow.Copy _
    Destination:=Worksheets(Target.Value).Range("B6553 6").End(xlUp).Offset(1, -1)

    Here, we move from the last cell in column B up until we find a non-blank cell. Offset(1, -1) moves one row down (to the first blank cell) and one column to the left (to column A; we must paste there since we paste an entire row.)

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula unknown (excel 2000)

    This code will fail in three cases. If the user capatalizes any of the letters in "win" or "loss" or if the user copies range of cells and pastes it into the test sheet and the range contains one or more cells in column D and the first cell in the copied range is not in column D, or if more than one cell in column D is pasted. I would prefer this adaptation of Hans code:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, ActiveSheet.Columns(4)) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, ActiveSheet.Columns(4))
    If UCase(oCell.Value) = "WIN" Or UCase(oCell.Value) = "LOSS" Then
    Application.EnableEvents = False
    oCell.EntireRow.Copy _
    Destination:=Worksheets(oCell.Value).Range("B65536 ").End(xlUp).Offset(1, -1)
    Application.EnableEvents = True
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula unknown (excel 2000)

    Yes, that's more robust. Thanks.

Posting Permissions

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