Compare 2 columns on 2 different worksheets and copy to 2nd worksheet if cell does not meet criteria

[nzamparo]

Hi all!

I'm hoping that someone can provide some direction on a code I'm currently working on.

I have a workbook with multiple worksheets, and would like to compare Column "L" on the "TransitionGrid-Dish" worksheet to Column A in the "LikeProfiles" worksheet. If the Column L values on the first worksheet are not contained in Column A, I would like to be able to copy them to the next blank row in Column A on the second worksheet.

I've come up with the following code so far:

Sub CompareandHardcode()

Dim ws As Worksheet
    Dim wb As Workbook
     
    Set wb = ActiveWorkbook
    Set ws = wb.Worksheets(1)

i = 1
Do
If Sheets("TRANSITIONGRID-DISH").Cells(i, 12).Value = "" Then
        Exit Do
    End If
    Set valFound = Sheets("LIKEPROFILES").Columns("A").Find(Sheets("TRANSITIONGRID-DISH").Cells(i, 12).Value, , , xlWhole)
    If valFound Is Nothing Then
        lastrow = Sheets("LIKEPROFILES").UsedRange.Rows.Count
        Sheets("LIKEPROFILES").Range("A" & lastrow & ":A" & lastrow) = Sheets("TRANSITIONGRID-DISH").Range("L" & i & ":L" & i)
    End If
    i = i + 1
Loop
    With ws
         
    End With
End Sub

Can anyone please tell me if I'm on the right track?! I feel so close, but the code as written above doesn't execute any action. I would really, really appreciate any help anyone would be kind enough to offer!

Nikki

 

[hiker95]

nzamparo,

It would have been better if you had supplied screenshots of all three worksheets.

The test workbook contained worksheets TransitionGrid-Dish, LikeProfiles, and second.

Sample worksheets:

Excel 2007
	L
1	123
2	234
3	345
4	456
5	467
6	100
7	200
8	678
9	300
10	789
11	400
12
TransitionGrid-Dish

Excel 2007
	A
1	Title A
2	123
3	234
4	456
5	467
6	200
7	678
8	300
9	400
10
LikeProfiles

Excel 2007
	A
1	Title A
2
3
4
5
second

After the macro:

Excel 2007
	A
1	Title A
2	345
3	100
4	789
5
second

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CompareandHardcodeV2()
' hiker95, 03/23/2013
' http://www.mrexcel.com/forum/excel-questions/693174-compare-2-columns-2-different-worksheets-copy-2nd-worksheet-if-cell-does-not-meet-criteria.html
Dim i As Long, lr As Long, fr As Long, nr As Long
With Sheets("TransitionGrid-Dish")
  lr = .Cells(Rows.Count, 12).End(xlUp).Row
  For i = 1 To lr Step 1
    fr = 0
    On Error Resume Next
    fr = Application.Match(.Cells(i, 12), Sheets("LikeProfiles").Columns(1), 0)
    On Error GoTo 0
    If fr = 0 Then
      nr = Sheets("second").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
      Sheets("second").Cells(nr, 1).Value = .Cells(i, 12).Value
    End If
  Next i
End With
Sheets("second").Activate
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CompareandHardcodeV2 macro.