Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
This returns the following in Excel:
rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)"
Sub VLookUpAutomation() Dim cell As Range Dim rng1, rng2 As Range Application.ScreenUpdating = False ' Select Sheet or set for just active sheet or provide combo box? Columns("B:B").Insert Shift:=xlToRight ' Insert column for vlookup formula to reside Range("B1").Value = "VL" ' Indicate which column is vlookup, by putting in text VL ' Set range for vlookup formula and fill down in column B Set rng1 = Range("C2", Range("C2").End(xlDown)).Offset(, -1) rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)" ' Test for Error (#N/A), when found, copy cell to left (, -1) and to right (, 1) then paste on sheet 2 ' Re-name (Name) Sheet 2 so that user changes will not effect which page "Corrected Names are Stored" On Error Resume Next For Each cell In rng1.SpecialCells(xlCellTypeFormulas, xlErrors) If cell.Value = CVErr(xlErrNA) Then With CorrectedAgentIDName If IsError(Application.Match(cell.Offset(, -1), .Columns(1), 0)) Then cell.Offset(, -1).Copy .Range("A" & Rows.Count).End(xlUp)(2) cell.Offset(, 1).Copy .Range("B" & Rows.Count).End(xlUp)(2) End If End With End If Next cell On Error GoTo 0 ' Set range to vlookup values from B2:Bn - Copy Range ' PasteSpecial Values into C2:Cn Set rng2 = Range("B2", Range("B2").End(xlDown)) rng2.Copy Range("C2").PasteSpecial xlPasteValues ' Delete column B Set rng2 = Range("B1", Range("B2").End(xlDown)) rng2.Delete Columns("A:B").AutoFit ' Fit Columns A and B to be visible Range("A2").Select ' Set cursor at Cell "A2" Application.ScreenUpdating = True End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.