Where am i going wrong

[Siyanna]

Hi Guys, where am i going wrong.

All i want to do is loop until there is a match. I promt user to enter a date between 2 dates. If there is no match i want the user to keep trying until the correct date is input else if they press cancel then Exit Sub

This is what i have so far

Set Rng = Sheets("Data").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)


If Not Matchdate Is Nothing Then
Matchnum = Matchdate.Row
Else
Do
MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets ("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
Loop Until Matchdate = Rng.Find(UserForm1.MonthView1.Value)
End If
Matchnum = Matchdate.Row

Thank You

 

[Siyanna]

Hi Guys, where am i going wrong.

All i want to do is loop until there is a match. I promt user to enter a date between 2 dates. If there is no match i want the user to keep trying until the correct date is input else if they press cancel then Exit Sub

This is what i have so far

Set Rng = Sheets("Data").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)


If Not Matchdate Is Nothing Then
Matchnum = Matchdate.Row
Else
Do
MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets ("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
Loop Until Matchdate = Rng.Find(UserForm1.MonthView1.Value)
End If
Matchnum = Matchdate.Row

Thank You

Please Guys....Any Luck

 

[cstimart]

Just a quick guess...

Set Rng = Sheets("Data").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)

Do Until Matchdate = Rng.Find(UserForm1.MonthView1.Value)
    MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
Loop

 

[Siyanna]

Hi,

I get an error message Run time error 91, object variable or with block variable not set

This is what i have


Set Rng = Sheets("List").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)

Do Until Matchdate = Rng.Find(UserForm1.MonthView1.Value)
MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
Loop
Matchnum = Matchdate.Row

 

[Siyanna]

Any Luck guys...

I am trying to keep my eyes open hoping to resolve this before i go to bed

 

[Siyanna]

Please Pleeeeeeeeeeeeease Pleeeeeeeeeeeeeeeeeeeeeeeease

My eyes are hurting

 

[cstimart]

Try this

Set Rng = Sheets("Data").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)

With Application
    Do While .IsNA(.Match(UserForm1.MonthView1.Value, Rng)) = True
         MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
    Loop
End With

Is this code in a Module? or in a Form?

 

[Siyanna]

Try this

Set Rng = Sheets("Data").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)
 
With Application
    Do While .IsNA(.Match(UserForm1.MonthView1.Value, Rng)) = True
         MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
    Loop
End With

Is this code in a Module? or in a Form?

The code is in a command button on a user form, is that classed as userform or module?

 

[tusharm]

So, what is the problem? And, what is the consumer expected to enter in MonthView1?

Hi Guys, where am i going wrong.

All i want to do is loop until there is a match. I promt user to enter a date between 2 dates. If there is no match i want the user to keep trying until the correct date is input else if they press cancel then Exit Sub

This is what i have so far

Set Rng = Sheets("Data").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)


If Not Matchdate Is Nothing Then
Matchnum = Matchdate.Row
Else
Do
MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets ("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
Loop Until Matchdate = Rng.Find(UserForm1.MonthView1.Value)
End If
Matchnum = Matchdate.Row

Thank You

 

[Siyanna]

I have a major problem with code where it loops but does not give me the option to enter another date. The message box does not allow me to cancel also and therefore my system just crashed

I have a button on my sheet (Load Form)

Once selected this code runs

Sub LoadForm()
Dim LastWsRow As Long
LastWsRow = Sheets("List").Cells(Rows.Count, "B").End(xlUp).Row
MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), , "ALERT"
UserForm1.Show
End Sub

Then my userform (Calender) Opens and propmts me to enter a date. I have an Submit command button on my userform where i have put the code below

Private Sub CMD1_Click()

Dim LastWsRow As Long
Dim Matchdate As Range
Dim Rng As Range
Dim Matchnum As Long
LastWsRow = Sheets("List").Cells(Rows.Count, "B").End(xlUp).Row



Set Rng = Sheets("List").Range("B4:B" & LastWsRow)
Set Matchdate = Rng.Find(UserForm1.MonthView1.Value)

With Application
Do While .IsNA(.Match(UserForm1.MonthView1.Value, Rng)) = True
MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), vbRetryCancel, "ALERT"
Loop
End With
Matchnum = Matchdate.Row

Monthview1 is the calender. I need to enter a date between the range
Sheets("List").Range("B4:B" & LastWsRow). If i enter a date that does not match then loop and promt me to enter a date until the date selected is in that range. Once found carry on with the rest of the code.
If the date is not in the range and If i press cancel or X then exit the sub.