Setting "r" to = range selected in an IF statement?
 

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

Setting "r" to = range selected in an IF statement?
 

Simon

Difficult to understand, but it looks like you are checking a day and then
want the range applicable to that day to be referenced by r and then iterate
through that range.. If so, then take the select of the and of each range
(As you don't need to select it) and add before

Set r =

If you want an object variable then you have to add set before it.

After you have done you for...next loop, set the variable r to Nothing,
before the code check other values

Set r = Nothing


Just for info, you variables w, vf and t will all be Variants, as you have
not declared them as anything, but I notice you explicitly used a Variant
type for t1...any reason? It is always better to use types in all variables
as whilst it may spring more data type errors, it does make debugging much
easier
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Simon Lloyd"
wrote in message
...

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

Setting "r" to = range selected in an IF statement?
 

Thanks for the reply nick, any non standard useages you see in my code
is because i haven't the foggiest what i'm doing!, that aside i did try
Set r = but it came up with it doesn't support this method or if i
changed it it said object required, What i am tryin to do is....if
UserForm2.ComboBox3 has "x" then select Range(Ax:Axx) if it contains xx
then.....etc once this range is selected i want to find the matching
value for Userform2.Listbox1 in this range select it then check the
offsets 1,3,5,7,9 & 11 for any values or text if so then msgbox....and
the rest should be straight forward...i hope!. I felt i had to do it
this way as the value that appears in Listbox1 would appear several
times on the selected sheet, but i only want to find it in the range
that has been selected because of the value in ComboBox3.

This is probably still as clear as mud to you....its a way for my
partner to find out if she has time slots available on which week,
which day and which part of the day!

Hope you don't mind helping further!

Regards,
Simon


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

Setting "r" to = range selected in an IF statement?
 

hi
instead of ..
"worksheets(w).range(x,x).select
try.....
set r= worksheets(w).range(x,x)

--
hemu


"Simon Lloyd" wrote:


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

Setting "r" to = range selected in an IF statement?
 

Hemant, thanks for the reply, i had previously used your suggestion, my
original coding was exactly like that but when it found the day i was
looking for it would stop the code and give ..Object variable not
set...or something along those lines.

Regards,
Simon


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