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