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
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