Help modifying my VBA code to output another column

kfg1287

New Member
Joined
Mar 4, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Worksheet1.PNG
    Worksheet1.PNG
    94.1 KB · Views: 5
  • Worksheet2.PNG
    Worksheet2.PNG
    91.4 KB · Views: 5
  • Worksheet3.PNG
    Worksheet3.PNG
    104.3 KB · Views: 5

kfg1287

New Member
Joined
Mar 4, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
sheet 3 is column D
sheet 4 is column F
 

Attachments

  • Worksheet3.PNG
    Worksheet3.PNG
    104.3 KB · Views: 3
  • Worksheet4.PNG
    Worksheet4.PNG
    82.9 KB · Views: 3

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Ok use
VBA Code:
   var1 = Sheet4.Range("D3", Sheet4.Range("D" & Rows.Count).End(xlUp)).Resize(, 3).Value2
and
VBA Code:
         ar1(n, 2) = var1(i, 3)
 

kfg1287

New Member
Joined
Mar 4, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Ok use
VBA Code:
   var1 = Sheet4.Range("D3", Sheet4.Range("D" & Rows.Count).End(xlUp)).Resize(, 3).Value2
and
VBA Code:
         ar1(n, 2) = var1(i, 3)
Worked beautifully. I commend you.

thank you very much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top