Now my macro works for "If Col N find a match in Col Y then copy values from Col Z [Y Offset(0, 1)] back to Col N"
Set DEST = Sheets("Mil")
Set DATA = Sheets("Scr")
Set Rng1 = DATA.Range(Range("Y2"), DATA.Range("Y" & Rows.Count).End(xlUp)) ' Data to copy from
Set Rng2 = DATA.Range(Range("N2"), DATA.Range("N" & Rows.Count).End(xlUp)) ' Data to replace
For Each c In Rng2
On Error Resume Next
' If Col N find the match in Col Y then copy Z [Y Offset(0, 1)] to Col N
Rng1.Find(What:=c).Offset(0, 1).COPY Destination:=c.Offset(0, 0)
Err.Clear
Next c
I would like to add following condition:
"If Col N doesn't find a match in Col Y then add Col N value to the last row of Col Y and sort Col Y:Z alphabetically"
Any help appreciated...
As highlighted in Dai's comment, it's best to avoid using "On Error Resume Next" unless you are certain that a specific error needs to be deliberately ignored.
If no matching data is found in Column Y, the Find method returns Nothing. In such cases, attempting to reference the result directly will raise a runtime error. I assume that's why you've used On Error Resume Next in your script.
Rng1.Find(What:=c).Offset(0, 1).COPY Destination:=c.Offset(0, 0)
Set Rng1 = DATA.Range(Range("Y2"), DATA.Range("Y" & Rows.Count).End(xlUp)) is equivalent to Set Rng1 = DATA.Range(ActiveSheet.Range("Y2"), DATA.Range("Y" & Rows.Count).End(xlUp)). If the active sheet is not DATA, this code will trigger error 1004.Pls try:
Set DEST = Sheets("Mil")
Set Data = Sheets("Scr")
Set Rng1 = Data.Range("Y2", Data.Range("Y" & Data.Rows.Count).End(xlUp)) ' Data to copy from
Set Rng2 = Data.Range("N2", Data.Range("N" & Data.Rows.Count).End(xlUp)) ' Data to replace
Dim ce As Range
For Each c In Rng2
Set ce = Nothing
Set ce = Rng1.Find(What:=c.Value, LookIn:=xlValues, lookat:=xlWhole)
If ce Is Nothing Then ' no matching in Col Y
Data.Range("Y" & Data.Rows.Count).End(xlUp).Offset(1).Value = c.Value
With Data.Range("Y:Z")
.Sort Key1:=.Cells(1), Header:=xlYes
End With
Else
ce.Offset(0, 1).Copy c
End If
Next c