Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help on code (2003)

    Hans very kindly provided me with some code that I have attempted to modify (unsuccessfully) for a different scenario.
    I am trying to match based on a concatenate of B&I. Then update F,G,H,J & K in Rejections 2 with the the same columns in purged 1 and then L in Rejections 2 with L from purged 2. The code executes, but incorrectly. Please could someone tellme where I am going wrong....

    Many Thanks
    Nathan
    Attached Files Attached Files

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

    Re: Help on code (2003)

    You're mixing up the sheets. In a line such as

    ws1.Cells(r2, 6) = ws2.Cells(r1, 6)

    you're using the row number for ws1 in ws2 and vice versa. It should be

    ws1.Cells(r1, 6) = ws2.Cells(r2, 6)

    Also, in the loop for ws3, you should start at m3, not at m2, and you should refer to ws3, not to ws2.

    And you don't check whether the value was actually found. See the code I wrote for you earlier.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on code (2003)

    Sorry Hans,

    I'm getting confused with the last bit....

    ' Loop through rows of Rejections 2
    For r2 = m3 To 2 Step -1

    ' Look for a match in Purged 2
    Set rngFound = rng1.Find(What:=ws3.Cells(r2, 13), LookIn:=xlValues, LookAt:=xlWhole)
    ' If found, update:
    r2 = rngFound.Row
    ws1.Cells(r1, 12) = ws3.Cells(r2, 12)

    Next r2

    I can't work out what's wrong?

    Thanks
    Nathan

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

    Re: Help on code (2003)

    In the first place, you should declare and use a variable r3, and use that for ws3 instead of r2.

    In the second place, your definition of rng3 is incorrect, it refers to m2 but it should refer to m3 instead:

    Set rng3 = ws3.Range("M2:M" & m3)

    In the third place, although your comment says "If found, ...", you don't actually check whether the value has been found.

    For r3 = m3 To 2 Step -1
    ' Look for a match in Purged 2
    Set rngFound = rng1.Find(What:=ws3.Cells(r3, 13), LookIn:=xlValues, LookAt:=xlWhole)
    If Not rngFound Is Nothing Then
    ' If found, update:
    r1 = rngFound.Row
    ws1.Cells(r1, 12) = ws3.Cells(r3, 12)
    End If
    Next r3

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on code (2003)

    This appears to work?? I think it's right but would appreciate your clarification.

    Many Thanks
    Nathan
    Attached Files Attached Files

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

    Re: Help on code (2003)

    This is much better!

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help on code (2003)

    Thankyou Hans!! With your (and others) help, I'm 90% of the way through automating a 90 minute daily chore!! I'm looking forward to looking forward to going to work. :-))

    Thanks

    Nathan

Posting Permissions

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