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]

Then you can describe in words what you need.

 

[Pinaceous]

I'll try. Thank you for asking.

I have a sheet where the user inputs a date between sDate1& sDate2 into Column B in the Range of (“B6:B66”).


I want a Sub to be created, where if run upon a blank sheet (where no dates are present the Column B in the Range of (“B6:B66”)) the code will produce a message box to the effect of MsgBox " NO Dates Listed".

If there are dates posted between sDate1 & sDate2 in Column B in the Range of (“B6:B66”) a message box will produce a message box to the effect of MsgBox " Valid Dates Listed ".

If there are both blank entries and dates posted between sDate1& sDate2 in Column B in the Range of (“B6:B66”) a message box will produce a message box to the effect of MsgBox " Valid Dates Listed " and will override the MsgBox " NO Dates Listed " which will not appear.


At this point I am not concerned about any dates that might pop up outside of sDate1 & sDate2 in Column B in the Range of (“B6:B66”).

 

[DanteAmor]

I'm sorry but I still don't clearly understand what the request is.


You could explain with an example each of the events you want to validate.

 

[Pinaceous]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]Row 6
[/TD]
[TD]11/13/19
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 66
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Date entry of 11/13/19 is between sDate1 (10/1/19) & sDate2 (9/30/20) in Column B and is in the Range of (“B6:B66”) a message box will produce a message box to the effect of MsgBox " Valid Dates Listed ".



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]Row 6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 66
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


A blank sheet (where no dates are present the Column B inthe Range of (“B6:B66”)) the code will produce a message box to the effect of MsgBox" NO Dates Listed ".


At this point, I am not concerned about any dates that might pop up outside of sDate1 & sDate2 in Column B in the Range of (“B6:B66”).

I hope this helps explain what I would like.

Thanks again Dante Amor for considering my dilemma.

R/
pinaceous

 

[DanteAmor]

I still don't understand very well what you want.


Let's start with the following.

Sub test1()
  Dim wCount As Variant, c As Range
  Dim sDate1 As Date, sDate2 As Date
  sDate1 = #10/1/2019#
  sDate2 = #9/30/2020#
  wCount = WorksheetFunction.Count(Range("B6:B66"))
  If wCount = 0 Then
    MsgBox "NO Dates Listed"
  Else
    For Each c In Range("B6:B66")
      If c <> "" Then
        If IsDate(c) Then
          If sDate1 <= c And c <= sDate2 Then
            MsgBox "Valid Dates Listed"
          Else
          
          End If
        End If
      End If
    Next
  End If
End Sub

 

[Pinaceous]

Hi Dante Amor,

I do like your code and it seems like you understand it very well.

For your code posted#15, if I have more than one entry it provides a message box for every entry.

Could you kindly revise your code to provided only one message box for multiple entries?

For example,

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[/TR]
[TR]
[TD]row 6
[/TD]
[TD]11/10/19
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 7
[/TD]
[TD]11/11/19
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 8
[/TD]
[TD]11/12/19

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]row 66
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



If I have three valid entries as can be seen here, 11/10/19, 11/11/19, & 11/12/19, can you have the message box of

 MsgBox "Valid Dates Listed"

please provide only one message box pop-up for multiple entries?

Thank you,
pinaceous

 

[DanteAmor]

After msgbox "valid dates" line add this line:

Exit For

 

[Pinaceous]

Thank you Dante Amor for helping me with my requests even though it was not traditional and seemed strange. I just wanted to say thank you! ?

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Pinaceous]

Hi DanteAmor,

If I place the Exit For after the MsgBox I get an error.

Sub test1()
  Dim wCount As Variant, c As Range
  Dim sDate1 As Date, sDate2 As Date
  sDate1 = #10/1/2019#
  sDate2 = #9/30/2020#
  wCount = WorksheetFunction.Count(Range("B6:B66"))
  If wCount = 0 Then
    MsgBox "NO Dates Listed"
    Exit For
    'End Sub
  Else
    For Each c In Range("B6:B66")
      If c <> "" Then
        If IsDate(c) Then
          If sDate1 <= c And c <= sDate2 Then
            MsgBox "Valid Dates Listed"
          Else
         
          End If
        End If
      End If
    Next
  End If
End Sub

Any suggestions?

Thank you!
Pinaceous