On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.
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.