PDA

View Full Version : VBA Lookup Errors



fdebelo
04-15-2011, 08:04 AM
I am new to VBA and have a simple lookup that I am getting an error on.

This formula looks for a name in COL D and fills in COL B with "Verified" if it finds the name- john - in COL A.

It works fine until the name is not found, as in ROW 3, and i get an error.
The word - Verified- is in Cell A1 on Sheet 2. (not shown)


COL-A___COL-B____COL-D
ajohn____Verified___john
johns____Verified___john
jones____#VALUE!___john



This is my formula:


Sub Lookup_Names()
'Lookup names in a list of Employees
Dim x As Integer
Dim n As Integer
Dim Name As String
For n = 1 To 2
Name = Worksheets("Sheet2").Range("A" & n).Value
For x = 1 To 5
Worksheets("Sheet1").Range("b" & x).Select
If Selection.Value = "" Then
ActiveCell.FormulaR1C1 = "=SEARCH(RC[2],RC[-1])"
If ActiveCell > 0 Then
ActiveCell = Name
End If
End If
Next x
Next n
End Sub


Am I correct to use the Search function in this formula?

Frank

Simon Lloyd
04-15-2011, 09:43 PM
this will do it for youSub Lookup_Names()
'Lookup names in a list of Employees
Dim x As Integer
Dim n As Integer
Dim Name As String
For n = 1 To 2
Name = Worksheets("Sheet2").Range("A" & n).Value
For x = 1 To 5
Worksheets("Sheet1").Range("b" & x).Select
If Selection.Value = "" Then
ActiveCell.FormulaR1C1 = "=IF(ISERROR(SEARCH(RC[2],RC[-1]))," & Chr(34) & "Unverified" & Chr(34) & ",SEARCH(RC[2],RC[-1]))"
If ActiveCell <> "Unverified" Then
ActiveCell = Name
End If
End If
Next x
Next n
End Sub