VBA Find Non-Match Criteria in WS2 and copy Criteria to WS1

[CyrusTheVirus]

Hi, 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 Employee ID that does not match an Employee ID 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.


Example:[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]State[/TD]
[TD]Employee Name[/TD]
[TD]Employee ID[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Variance
[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Brown, Scott[/TD]
[TD]4545[/TD]
[TD]45[/TD]
[TD]-45[/TD]
[TD]20[/TD]
[TD]-15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Charleson, Brian[/TD]
[TD]1561[/TD]
[TD]35[/TD]
[TD]-37.5[/TD]
[TD]37.5[/TD]
[TD]-35[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]Matthews, Dorothy[/TD]
[TD]1234[/TD]
[TD]20[/TD]
[TD]-25[/TD]
[TD]25[/TD]
[TD]-25[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]Roberts, Chad[/TD]
[TD]9799[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]State[/TD]
[TD]Employee[/TD]
[TD]Employee ID[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Brown, Scott[/TD]
[TD]4545[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Charleson, Brian[/TD]
[TD]1561[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]CONN[/TD]
[TD]Coleman, Anthony[/TD]
[TD]4466[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]Carter, Randy[/TD]
[TD]4112[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[TD]Matthews, Dorothy[/TD]
[TD]1234[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]NH[/TD]
[TD]Torres, Jose[/TD]
[TD]8884[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

After finding the non-matches, bring the non-match items from Column A to C from 'Sheet2' over to 'Sheet1' to look like this (since they did not match):

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Sheet1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]State[/TD]
[TD]Employee Name[/TD]
[TD]Employee ID[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Deducted[/TD]
[TD]Paid[/TD]
[TD]Variance
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CONN[/TD]
[TD]Brown, Scott[/TD]
[TD]4545[/TD]
[TD]45[/TD]
[TD]-45[/TD]
[TD]20[/TD]
[TD]-15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CONN[/TD]
[TD]Charleson, Brian[/TD]
[TD]1561[/TD]
[TD]35[/TD]
[TD]-37.5[/TD]
[TD]37.5[/TD]
[TD]-35[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]CONN[/TD]
[TD]Coleman, Anthony[/TD]
[TD]4466[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]MD[/TD]
[TD]Carter, Randy[/TD]
[TD]4112[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]NH[/TD]
[TD]Torres, Jose[/TD]
[TD]8884[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]MD[/TD]
[TD]Matthews, Dorothy[/TD]
[TD]1234[/TD]
[TD]20[/TD]
[TD]-25[/TD]
[TD]25[/TD]
[TD]-25[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]MA[/TD]
[TD]Roberts, Chad[/TD]
[TD]9799[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]15[/TD]
[TD]-10[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

My code so far...

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 those EE's info over to 'Sheet1'.


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


End Sub

 

[CyrusTheVirus]

[COLOR=darkblue]Sub[/COLOR] CopyNonMatches()
    
    [COLOR=darkblue]Dim[/COLOR] ws1 [COLOR=darkblue]As[/COLOR] Worksheet, ws2 [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rngIDs1 [COLOR=darkblue]As[/COLOR] Range, vData [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws1 = ThisWorkbook.Sheets("Sheet1")
    [COLOR=darkblue]Set[/COLOR] ws2 = ThisWorkbook.Sheets("Sheet2")
    
    [COLOR=darkblue]Set[/COLOR] rngIDs1 = ws1.Range("C2", ws1.Range("C" & Rows.Count).End(xlUp))
    vData = ws2.Range("A2", ws2.Range("C" & Rows.Count).End(xlUp)).Value
    
    [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)
        [COLOR=darkblue]If[/COLOR] IsError(Application.Match(vData(i, 3), rngIDs1, 0)) [COLOR=darkblue]Then[/COLOR]
            ws1.Rows(8).Insert
            ws1.Range("A8:C8").Value = Array(vData(i, 1), vData(i, 2), vData(i, 3))
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] i
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Nevermind. It does work! I entered the information incorrectly. Sorry about that. Thanks so much! I have no idea how you know how to do this stuff so quickly. It's amazing! Thanks again!

 

[AlphaFrog]

You're welcome.

 

[CyrusTheVirus]

Hi AlphaFrog,

I know it's been a while since this post, but I was wondering if you could explain this part

[COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](vData, 1)

[/QUOTE]

I understand i = 1 to a certain limit, but could you clarify the 'UBound(VData, 1) part? And why the 1?

 

[AlphaFrog]

UBound returns the upper boundary of the 1st dimension (rows) of the array vData. In other words, it loops through the rows of the data.

TIP; in your code, highlight the UBound keyword and press F1 for help on that function.

 

[CyrusTheVirus]

Thanks, that does make sense. And thanks for the F1 tip, that was very helpful.

I do have one question though, I get a Run-time error '424': Object Required error on the below code. Do you happen to know which part I am writing incorrectly?

I am trying to have the cell 'C1' equal the 2nd row , 1st column of the array (A2).

Option Explicit
Sub Test()

Dim Voucher as Worksheet
Dim arr() as Variant

Set Voucher = ThisWorkbook.Worksheets("Sheet1")

arr = Voucher.Range("A1:A2").Value

Voucher.Range("C1").Value = arr(2, 1).Value

End Sub

 

[AlphaFrog]

There is no .Value property for an array. Just use...

Voucher.Range("C1").Value = arr(2, 1)

 

[CyrusTheVirus]

Thanks AlphaFrog. That works.

I have one more question for you, if you don't mind help answering.

Why does that code work, again here it is to clarify which code I'm talking about:

Option Explicit
Sub Test()

Dim Voucher as Worksheet
Dim arr() as Variant
Set Voucher = ThisWorkbook.WorkSheets("Sheet1")

arr = Voucher.Range("A1:A2").Value

Voucher.Range("C1").Value = arr(2, 1)

Why does that code work when I

Dim arr() as Variant

or when I just

Dim arr()

but it gives me a Run-time error '13': Type mismatch when coding it as

Dim arr() as Long

 

[AlphaFrog]

Only a Variant data type can accept the array of range values. I suppose it's because the range values could be any data type including a combination of types.

Dim arr() defaults to type Variant when no type is specified.