Help on code (2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Many Thanks
Nathan

Reply | Quote

This is much better!

Reply | Quote

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

Reply | Quote