VBA If statement

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
1,104
Office Version
  1. 365
Platform
  1. Windows
Dear All,

I have two dates, where I'd like a message box to pop, if the dates are between this criteria.

Code:
    sDate1 = #10/1/2019#
    sDate2 = #9/30/2020#


How would I create the following?

Code:
 If date is between sDate1 & sDate2 Then
                                        
             MsgBox " Dates Within Range" 
   
End If

Thank you!
pinaceous
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try this

Code:
Sub test()
  Dim sDate1 As Date, sDate2 As Date
  sDate1 = #10/1/2019#
  sDate2 = #9/30/2020#
  If Date >= sDate1 And Date <= sDate2 Then
    MsgBox "Dates Within Range"
  End If
End Sub
 
Upvote 0
Try this

Code:
Sub test()
  Dim sDate1 As Date, sDate2 As Date
  sDate1 = #10/1/2019#
  sDate2 = #9/30/2020#
  If Date >= sDate1 And Date <= sDate2 Then
    MsgBox "Dates Within Range"
  End If
End Sub

Wow thanks Dante Amor!
 
Upvote 0
Hey Dante Amor,

In building upon this concept if I have nothing listed in this range I can then write:

Code:
Sub test()
  Dim sDate1 As Date, sDate2 As Date
  sDate1 = #10/1/2019#
  sDate2 = #9/30/2020#
  If Date >= sDate1 And Date <= sDate2 Then
    MsgBox "Dates Within Range"
  End If


 If Date <> Empty Then
    
        MsgBox " NO Dates Listed " 
   
  End If 

End Sub

But now how could I combine these two codes together onto one Sub?

Thank you!

Pinaceous
 
Last edited:
Upvote 0
The previously posted
Code:
 MsgBox "Dates Within Range"
should be just be interpreted as
Code:
 MsgBox " Valid Dates"
from
Code:
 sDate1 = #10/1/2019#
  sDate2 = #9/30/2020#
.
 
Last edited:
Upvote 0
Sorry, but I'm not understanding what your question is.
 
Upvote 0
Hi Dante Amor,

I understand now. Sorry about that! The range I’m working with is in Range("B8:B66").
This is where I have the user list the date between:
Code:
 sDate1= #10/1/2019#,  sDate2 = #9/30/2020#

So, now how can I make the following code work?

Code:
[FONT=Calibri][SIZE=3][COLOR=#000000]Sub test()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  Dim sDate1 As Date, sDate2 As Date[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  sDate1 = #10/1/2019#[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  sDate2 = #9/30/2020#[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]  If Date >= sDate1And Date <= sDate2 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    MsgBox "DatesWithin Range"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  End If[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000] If Date <>Empty Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        MsgBox " NO Dates Listed "[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]  End If [/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

Thank you,
Pinaceous
 
Last edited:
Upvote 0
Do you need a cycle to verify the dates you have in the range "B8:B66"?
 
Upvote 0
I'm pretty sure I don't because I have Private Sub Workbook code that cycles through the data that the user provides.
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

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