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

 

[AlphaFrog]

I would suggest you just exit the sub after you tell them to enter a new date. That will allow them to select a new date on MonthView1 and then they have to click on CMD1 again.

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)
    
[COLOR="Red"]    If Machdate Is Nothing Then
        MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), , "ALERT"
        Exit Sub
    End If[/COLOR]
    
    Matchnum = Matchdate.Row

 

[Siyanna]

I would suggest you just exit the sub after you tell them to enter a new date. That will allow them to select a new date on MonthView1 and then they have to click on CMD1 again.

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)
 
[COLOR=red]   If Machdate Is Nothing Then[/COLOR]
[COLOR=red]       MsgBox "Enter date between " & Sheets("List").Cells(4, "B") & " - " & Sheets("List").Cells(LastWsRow, "B"), , "ALERT"[/COLOR]
[COLOR=red]       Exit Sub[/COLOR]
[COLOR=red]   End If[/COLOR]
 
    Matchnum = Matchdate.Row

Why on earth did i not think of that. I pray one day i can see the things straight away just like you guys. Awesome. Thank You x. It was all there but i couldn't see it