Hi all, i am having trouble setting "r" to be the range selected during
the first IF statement, i am trying to get the code to select a
worksheet as defined on a userform (works ok!), then on the worksheet
select a range of times depending on the day selected, then i want it
to select the time found in Userform2.listbox1 and check the offsets
for entries if the offsets contain a value then msgbox blah blah....
the code below works so far but i'm seeing double trying to make it
work properly....Can anyone help?

Regards,
Simon

Public Sub FindSlot()
Dim strFirst As Integer
Dim rng As Range
Dim t1 As Variant
Dim w, vf, t
Dim r As Range
Dim mycell
Application.EnableEvents = False
w = UserForm2.ComboBox3.Value
vf = UserForm2.ListBox1.Value
Worksheets(w).Visible = True
Worksheets(w).Select
t = UserForm2.ComboBox1.Value
If t = "Tuesday" Then
Worksheets(w).Range("A4:A46").Select
ElseIf t = "Wednesday" Then
Worksheets(w).Range("A49:A94").Select
ElseIf t = "Thursday" Then
Worksheets(w).Range("A97:A142").Select
ElseIf t = "Friday" Then
Worksheets(w).Range("A145:A190").Select
ElseIf t = "Saturday" Then
Worksheets(w).Range("A193:A238").Select
End If
'NEED TO KNOW HOW TO SET r AS RANGE HERE!
On Error GoTo XIT
Application.EnableEvents = False

For Each mycell In r
If mycell.Value = vf.Value Then
mycell.Select
If mycell.Offset(0, 1) < "" Or mycell.Offset(0, 3) < "" Or
mycell.Offset(0, 5) < "" Or mycell.Offset(0, 7) < "" Or
mycell.Offset(0, 9) < "" Or mycell.Offset(0, 11) < "" Then
If MsgBox("Please Choose New Time, Day or Week... " & mycell.Value & "
Is Taken!", _
vbOKOnly, "Time Slot Taken") = vbOK Then
Exit Sub
ElseIf mycell.Offset(0, 1) = "" Or mycell.Offset(0, 3) = "" Or
mycell.Offset(0, 5) = "" Or mycell.Offset(0, 7) = "" Or
mycell.Offset(0, 9) = "" Or mycell.Offset(0, 11) = "" Then
If MsgBox("Click Yes to Make Booking or Click No To Exit", vbYesNo,
"Make A Booking?") = vbYes Then
Unload UserForm2
UserForm1.Show
End If
End If
End If
End If
Next
Worksheets(w).Visible = False
XIT:
Application.EnableEvents = True
Unload UserForm2
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=557563