Where am i going wrong

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
Just a quick guess...

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

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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
 
Last edited:

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146

ADVERTISEMENT

Any Luck guys...

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

Siyanna

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Please Pleeeeeeeeeeeeease Pleeeeeeeeeeeeeeeeeeeeeeeease

My eyes are hurting :(
 

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180

ADVERTISEMENT

Try this

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

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
Try this

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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

Well-known Member
Joined
Nov 7, 2011
Messages
1,146
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top