Help modifying my VBA code to output another column

[kfg1287]

My VBA knowledge is basic but my coworker on leave has left me with this code to modify. I attached the VBA code that compares worksheet 2, 3, 4, and outputs what's not in worksheet 2 to worksheet 1. It works great but I cant seem to figure out how to bring the other " Model" column associated with the "Ip address" cells result.

For example

Worksheet 2
column B = Ip address column C = Model

my code outputs the ip addresses from worksheet 2 to worksheet 1 column E but not the "Model"
Any modifications to input the model number associated with the ip address to worksheet 1 column F?



VBA code

Sub ()
'Excel vba to remove duplicates.

Dim dic As Object
Dim ar As Variant
Dim ar1 As Variant
Dim var As Variant
Dim i As Long
Dim n As Long

Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
ar = Sheet2.Range("B2", Sheet2.Range("B" & Rows.Count).End(xlUp)).Value
var = Sheet4.Range("D2", Sheet4.Range("D" & Rows.Count).End(xlUp)).Value
var = Sheet3.Range("C3", Sheet3.Range("C" & Rows.Count).End(xlUp)).Value
ReDim ar1(1 To UBound(var), 1 To 1)

'Loop through ar and add to Dictionary.
For i = 1 To UBound(ar)
If Not dic.exists(ar(i, 1)) Then
dic.Add ar(i, 1), ar(i, 1)
End If
Next i

'Identify non Matches
For i = 1 To UBound(var)
If Not dic.exists(var(i, 1)) Then
n = n + 1
ar1(n, 1) = var(i, 1)
End If
Next i

'Output Results Remove any Duplication
Sheet1.Range("E10:E" & UBound(var)).Value = ar1
Range("E10:E" & UBound(var)).RemoveDuplicates 1

End Sub



thank you so much in advance.

 

[kfg1287]

sheet 3 is column D
sheet 4 is column F

 

[Fluff]

Ok use

   var1 = Sheet4.Range("D3", Sheet4.Range("D" & Rows.Count).End(xlUp)).Resize(, 3).Value2

and

         ar1(n, 2) = var1(i, 3)

 

[kfg1287]

Ok use

   var1 = Sheet4.Range("D3", Sheet4.Range("D" & Rows.Count).End(xlUp)).Resize(, 3).Value2

and

         ar1(n, 2) = var1(i, 3)

Worked beautifully. I commend you.

thank you very much

 

[Fluff]

You're welcome & thanks for the feedback.