Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
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
EE-VBA-VLookUp-Automation-Versio.xls
For Each cell In rng.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error Resume Next
If cell.Value = CVErr(xlErrNA) Then
'MsgBox "Found an #N/A"
cell.Offset(, -1).Copy Sheet2.Range("A" & Rows.Count).End(xlUp)(2)
cell.Offset(, 1).Copy Sheet2.Range("B" & Rows.Count).End(xlUp)(2)
EE-VBA-VLookUp-Automation-Versio.xls
Sub VLookUpAutomation()
Dim cell As Range
Dim rng As Range
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 IsError(ActiveCell.Value) 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
EE-VBA-VLookUp-Automation-Versio.xls
Sub VLookUpAutomation()
Dim cell As Range
Dim rng As Range
Sheet1.Activate
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.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error Resume Next
If cell.Value = CVErr(xlErrNA) Then
'MsgBox "Found an #N/A"
With Sheet2
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
Else
'MsgBox "Not a #N/A"
End If
On Error GoTo 0
Next cell
End Sub
Sub VLookUpAutomation()
Dim cell As Range
Dim rng As Range
Sheet1.Activate
Columns("B:B").Insert Shift:=xlToRight
Range("B1").Value = "VL"
Set rng = Range("C2", Range("C2").End(xlDown)).Offset(, -1)
rng.FormulaR1C1 = "=vlookup(RC[-1],'Corrected ID-Name'!C1:C2,2,FALSE)"
On Error Resume Next
For Each cell In rng.SpecialCells(xlCellTypeFormulas, xlErrors)
If cell.Value = CVErr(xlErrNA) Then
With Sheet2
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
End Sub
Open in new window