excelvba

VBA For Each Loop and Else condition


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


Solution

  • Rng1.Find(What:=c).Offset(0, 1).COPY Destination:=c.Offset(0, 0)
    

    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