VBA If statement

[Pinaceous]

Dear All,

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

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

How would I create the following?

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

Thank you!
pinaceous

 

[DanteAmor]

Try this

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

 

[Pinaceous]

Try this

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!

 

[Pinaceous]

Hey Dante Amor,

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

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

 

[DanteAmor]

What range do you mean?

 

[Pinaceous]

The previously posted

 MsgBox "Dates Within Range"

should be just be interpreted as

 MsgBox " Valid Dates"

from

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

.

 

[DanteAmor]

Sorry, but I'm not understanding what your question is.

 

[Pinaceous]

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:

 sDate1= #10/1/2019#,  sDate2 = #9/30/2020#

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

[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

 

[DanteAmor]

Do you need a cycle to verify the dates you have in the range "B8:B66"?

 

[Pinaceous]

I'm pretty sure I don't because I have Private Sub Workbook code that cycles through the data that the user provides.