Simple VBA question
 

Can anybody tell me why this isn't working? I have a list of Last names in
A2:A6 and a list of first names in B2:B6. It works if I put a match in cells
G6 and H6, but gives me an error if there is no match. Thanks for the help.

Sub Macro1()
Dim a As String
Dim b As String
Dim c As Boolean
Dim d As Boolean

a = Range("G6").Value
b = Range("H6").Value
c = WorksheetFunction.IsError(WorksheetFunction.Match( a, Range("A2:A6"),
0))
d = WorksheetFunction.IsError(WorksheetFunction.Match( b, Range("B2:B6"),
0))

If c = False And d = False Then
MsgBox ("This name has already been entered" & Chr(13) _
& "please enter another name.")
End If
End Sub

Mark,

Try this

Dim a As String
Dim b As String
Dim c
Dim d

a = Range("G6").Value
b = Range("H6").Value
c = Application.Match(a, Range("A2:A6"), 0)
d = Application.Match(b, Range("B2:B6"), 0)
If Not IsError(c) And Not IsError(d) Then
MsgBox ("This name has already been entered" & Chr(13) _
& "please enter another name.")
End If


What if the names are found in different rows?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mark1" wrote in message
...
Can anybody tell me why this isn't working? I have a list of Last names
in
A2:A6 and a list of first names in B2:B6. It works if I put a match in
cells
G6 and H6, but gives me an error if there is no match. Thanks for the
help.

Sub Macro1()
Dim a As String
Dim b As String
Dim c As Boolean
Dim d As Boolean

a = Range("G6").Value
b = Range("H6").Value
c = WorksheetFunction.IsError(WorksheetFunction.Match( a,
Range("A2:A6"),
0))
d = WorksheetFunction.IsError(WorksheetFunction.Match( b,
Range("B2:B6"),
0))

If c = False And d = False Then
MsgBox ("This name has already been entered" & Chr(13) _
& "please enter another name.")
End If
End Sub