CODEWITHSUNDEEP
vbaexcel
Jackyson
Jackyson

Reputation: 23

Excel VBA for loop condition

I would like to ask about the for loop in VBA, I have the script below to remind ("remind" is a sub function) me if current date is CheckDate1,2,3,4,5,6... I would like to ask if I need to add more date, can I use a for loop to contain all the CheckDate(s) instead of adding more ElseIf function?

Sorry for my poor English and Thanks very much!

Dim dtmMyDate As Date

Const CheckDate1 = #1/9/2018#
Const CheckDate2 = #1/11/2018#
Const CheckDate3 = #1/16/2018#
Const CheckDate4 = #1/18/2018#
Const CheckDate5 = #1/23/2018#
Const CheckDate6 = #1/25/2018#

dtmMyDate = DateSerial(Year(Now()), Month(Now()), Day(Now()))
If dtmMyDate = CheckDate1 Then
    Remind
ElseIf dtmMyDate = CheckDate2 Then
    Remind
ElseIf dtmMyDate = CheckDate3 Then
    Remind
ElseIf dtmMyDate = CheckDate4 Then
    Remind
ElseIf dtmMyDate = CheckDate5 Then
    Remind
ElseIf dtmMyDate = CheckDate6 Then
    Remind
Else
    NormalCheck
End If

Upvotes: 2

Views: 448

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

Doing something like this would be more sustainable:

Load your dates into an Excel Range on a worksheet and Define a Dynamic Named Range as, say, MyDates and assign the scope at the Workbook Level. This way adding or removing dates will be very easy.

Then this can be your code:

Dim vDates as Variant
vDates = Range("myDates")

Dim lDate as Long

For lDate = LBound(vDates) to UBound(vDates)
    If vDates(lDate,1) = Format(Now,"m/d/yyyy") Then 
        Remind
        Dim bRemind as Boolean
        bRemind = True
        Exit For
    End If
Next

If Not bRemind Then NormalCheck

Upvotes: 4

Related Questions