Please see code:-

Sub Sponsoracsselection()
'
' Sponsoracsselection Macro
' Macro recorded 25/09/2006 by Administrator
'

Dim res1 As Variant
Dim res2 As Variant
Dim res3 As Variant
Dim res4 As Variant

res1 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 2, False)
res2 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 3, False)
res3 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 4, False)
res4 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 5, False)

If Worksheets("Data").Range("W2") = 1 Then

If Not IsError(res1) Then
Worksheets(res1).Select
Else
MsgBox "Match not made"
End If

Else

If Worksheets("Data").Range("W2") = 2 Then

If Not IsError(res2) Then
Worksheets(res2).Select
Else
MsgBox "Match not made"
End If

Else

If Worksheets("Data").Range("W2") = 3 Then

If Not IsError(res3) Then
Worksheets(res3).Select
Else
MsgBox "Match not made"
End If

If Worksheets("Data").Range("W2") = 4 Then

If Not IsError(res4) Then
Worksheets(res4).Select
Else
MsgBox ("Match not made")
End If


End If
End If
End If
End If

End Sub

----------------

The first 3 parts work. But the fourth does not. Is there any reason why
this is happening? (res4). The first three were set up orignially than the
4th added after.

Cheers
Matt

Spotted what Ive missed

an else anfter the third statement.


Is there an easier way of doing this btw?

"Matthew Balch" wrote:

Please see code:-

Sub Sponsoracsselection()
'
' Sponsoracsselection Macro
' Macro recorded 25/09/2006 by Administrator
'

Dim res1 As Variant
Dim res2 As Variant
Dim res3 As Variant
Dim res4 As Variant

res1 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 2, False)
res2 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 3, False)
res3 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 4, False)
res4 = Application.VLookup(Sheets("Data").Range("SheetLoo kup"), _
Worksheets("Data").Range("DataDB"), 5, False)

If Worksheets("Data").Range("W2") = 1 Then

If Not IsError(res1) Then
Worksheets(res1).Select
Else
MsgBox "Match not made"
End If

Else

If Worksheets("Data").Range("W2") = 2 Then

If Not IsError(res2) Then
Worksheets(res2).Select
Else
MsgBox "Match not made"
End If

Else

If Worksheets("Data").Range("W2") = 3 Then

If Not IsError(res3) Then
Worksheets(res3).Select
Else
MsgBox "Match not made"
End If

If Worksheets("Data").Range("W2") = 4 Then

If Not IsError(res4) Then
Worksheets(res4).Select
Else
MsgBox ("Match not made")
End If


End If
End If
End If
End If

End Sub

----------------

The first 3 parts work. But the fourth does not. Is there any reason why
this is happening? (res4). The first three were set up orignially than the
4th added after.

Cheers
Matt