MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
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
|how to Classify of email (pdf attachments)||7||32|
|Copy the value of Cell C5 into sheet2 Cell B5 if A1 on sheet 1 = (MCL). If cell C5 is empty (or 'No') do nothing. Is this possible?||7||25|
|formula how to get the number incrementor?||3||21|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!