# Thread: Help on code (2003)

1. ## 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

2. ## 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. ## 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. ## 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. ## Re: Help on code (2003)

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

Many Thanks
Nathan

6. ## Re: Help on code (2003)

This is much better!

7. ## 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
•