We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Sub VLookUpAutomation() Dim cell As Range Dim rng As Range ' Run with Sheet1 as active sheet ActiveSheet.Columns("B:B").Insert Shift:=xlToRight ActiveSheet.Range("B1").Value = "VL" ActiveSheet.Range("B2").FormulaR1C1 = _ "=vlookup(RC[-1],'Corrected ID-Name'!C1:C2,2,FALSE)" Set rng = [B2] Set rng = Range(rng, Cells(Cells(Rows.Count, 1).End(xlUp).Row, rng.Column)) If rng.Rows.Count = 1 Then rng.Select Else rng.Cells(1, 1).AutoFill Destination:=rng, Type:=xlFillDefault End If For Each cell In rng If cell = "#N/A" Then MsgBox "Found an #N/A" 'copy cell to left and copy cell to right 'paste onto Correct Agent ID-Name 'Using first open row at bottom 'Will vlookup update so that repeated #N/A's are not copyied again? 'If not, do I need to say "recalculate" prior to going on to next cell looking for #N/A Else MsgBox "Not a #N/A" End If Next cell 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.