Sub to update data

[Max]

I've got source data in sheet: x
IDs are listed in C2 down,
Date of Deletion in R2 down
(data is ~ 40k rows, as per last data in ID col C)

In another sheet: z (received daily)
IDs are listed in C2 down, Date of Deletion in J2 down

I'd like to run a sub to update x's col R with the deletion dates in z's col
J for IDs in z which match those in x. For unmatched IDs, do nothing in x,
but flag these IDs in z's col K as: "ID not found". IDs are text. Thanks

 

[Joel]

Sub GetDelectionDate()

With Sheets("Z")
Lastrow = .Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 2 To Lastrow
ID = .Range("C" & RowCount)
Set c = Sheets("X").Column("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("K" & RowCount) = "ID not Found"
Else
.Range("J" & RowCount) = c.Offset(0, 15)
End If
Next RowCount
End With

End Sub

 

[Max]

Thanks, Joel

Tried running your sub but hit with:

RunTime error 438
Object doesn't support this property or method

Debug pointed to this line:

Set c = Sheets("X").Column("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

(I'm using xl2003)

 

[Joel]

should be Columns

from
Set c = Sheets("X").Column("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
to
Set c = Sheets("X").Columns("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)

 

[Max]

Thanks, that fixed the error. But the sub is doing the 1st part of it in
reverse, ie it is updating z from x, when it should be the other way, update
x from z, ref:

update x's col R with the deletion dates in z's col J
for IDs in z which match those in x

The 2nd part is working fine though

 

[Joel]

I got one line backwards

from
.Range("J" & RowCount) = c.Offset(0, 15)
to
c.Offset(0, 15) = .Range("J" & RowCount)

 

[Max]

Many thanks. That got it going great.

I need a slight refinement, though. As x's col C may contain duplicate IDs,
the updates from z would need to update all of these cases in x's col R.
Currently the sub does update it correctly but only for the 1st instance in
x's col R. The 2nd, 3rd instances remain un-updated.

 

[Joel]

Try this. It is easier to go through each sheet seperately.

Sub GetDelectionDate()

With Sheets("Z")
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
ID = .Range("C" & RowCount)
Set c = Sheets("X").Columns("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
.Range("K" & RowCount) = "ID not Found"
End If
Next RowCount
End With
With Sheets("X")
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
ID = .Range("C" & RowCount)
Set c = Sheets("Z").Columns("C").Find(what:=ID, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Range("R" & RowCount) = c.Offset(0, 7)
End If
Next RowCount
End With


End Sub

 

[Max]

Brilliant, Joel. Thanks. Works fine.