0

I do not have 10 reputation, so I'm unable to uploaded images, which would make this much, MUCH easier to explain... i posted a table example over to Mr. Excel forum here: http://www.mrexcel.com/forum/excel-questions/833202-visual-basic-applications-find-non-match-criteria-ws2-copy-criteria-ws1.html

I have two worksheets. I need to find Employee ID's in "Sheet2" that match the Employee ID's in "Sheet1". If "Sheet2" has an ID that is not in "Sheet1", then I need to copy specific cells from the said row of "Sheet2" over to "Sheet1".

On top of that, when copying over, I need to make sure that a whole row inserts for the copied cells in order for the previous $amounts to be in the correct spot (see the post in Mr. Excel). This probably makes no sense. If only I could upload an image...

Option Explicit

Sub CopyNonMatches()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim ws1Range As Range, ws2range As Range
Dim ws1Long As Long, ws2long As Long

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

With ws1
ws1Long = .Range("C" & .Rows.Count).End(xlUp).Row
End With

Set ws1Range = ws1.Range("C3", "C" & ws1Long)

With ws2
ws2long = .Range("C" & .Rows.Count).End(xlUp).Row
End With

Set ws2range = ws2.Range("C3", "C" & ws2long)

'Now I need to compare the ranges 'ws1Range' with 'ws2Range' and if 'ws2Range' has ID's that...
'...are not included in 'ws1Range', then I need to copy over info from Columns A to C from the row that had no match over to 'Sheet1'.

???????????????????

End Sub
CyrusTheVirus
  • 18
  • 7

1 Answers1

0

Sub CopyNonMatches()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim vIDs1 As Variant, vData As Variant
Dim i As Long

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

vIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp)).Value
vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value

For i = 1 To UBound(vData, 1)
    If IsError(Application.Match(vData(i, 3), vIDs1, 0)) Then
        ws1.Rows(8).Insert
        ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
    End If
Next i

End Sub

CyrusTheVirus
  • 18
  • 7