[vba] find next day

[Antman404]

Hello all you helping ones.
Thank you in advance for reading and for your help, really appreciate it

i am trying to find the next day based on checked boxes
there are 7 checkboxes: 1 is sunday (i based it on excels weekday values), hence 7 is saturday
if the user checks e.g. the second box, which is Monday (Value = 2), and types in a date (day, month and year; splitted to 3 boxes), which is in this example a Tuesday (Value = 3), the code should find the next date of a Monday.
My Code looks like this

If CheckBox1 = True Then 'Sunday is checked
    c1 = 1
End If
If CheckBox2 = True Then 'Monday is checked
    c2 = 1
End If
If CheckBox3 = True Then 'Tuesday is checked
    c3 = 1
End If
If CheckBox4 = True Then 'Wednesday is checked
    c4 = 1
End If
If CheckBox5 = True Then 'Thursday is checked
    c5 = 1
End If
If CheckBox6 = True Then 'Friday is checked
    c6 = 1
End If
If CheckBox7 = True Then 'Saturday is checked
    c7 = 1
End If

DateBefore = Weekday(y2.Cells(x + 1, "F")) 'That is where the typed in date comes from

If c1 = 1 Or c2 = 1 Or c3 = 1 Or c4 = 1 Or c5 = 1 Or c6 = 1 Or c7 = 1 Then

    n = 1
    Do Until c & (DateBefore + n) = 1 [COLOR=rgb(226, 80, 65)]'the Problem is HERE, no Variable will be found, infinite loop[/COLOR]
    
        If DateBefore + n > 7 Then
        
            DateBefore = -7 + DateBefore
        
        End If
        
        n = n + 1
    
    Loop
    
    DateAfter = y2.Cells(x + 1, "F") + n

Else

    DateAfter = y2.Cells(x + 1, "F") + 1

End If

How do i declare the c & (DateBefore + n) Variable to find the next c(#num) variable?
I am also open for a better solution than 7 times "If Then"

 

[Anthony47]

I would use RadioButtons (OptionButtons), that are mutually exclusive, rather than CheckBoxes

Anyway, something like

StartDate = Range("D1").Value             <<< MODIFY this so StartDate contains your initial date
'Complete the If /ElseIf structure for all of your controls:
If CheckBox1 Then
    lFor = 1
ElseIf CheckBox2 Then
    lFor = 2
ElseIf CheckBox3 Then
    lFor = 3
'..
'..
'..
ElseIf CheckBox7 Then
    lFor = 7
End If
FinDate = StartDate + (7 - Weekday(StartDate) + lFor) Mod 7

FinDate will hold the target date

Try...

 

[Antman404]

Thankyou alot for your effort, Anthony
well, Optionbuttons are - in my case - no option (haha)
in a regular case the user would check more than just one box and i need the next date of the next checked box, which might be a monday or a tuesday or whatever days are checked

i just solved it now the following way, although i happen to find it not that elegant

Dim c As Object
Set c = CreateObject("Scripting.Dictionary")

For i = 1 To 7
c(i) = 0
Next

If CheckBox1 = True Then
    c(1) = 1
End If
If CheckBox2 = True Then
    c(2) = 1
End If
If CheckBox3 = True Then
    c(3) = 1
End If
If CheckBox4 = True Then
    c(4) = 1
End If
If CheckBox5 = True Then
    c(5) = 1
End If
If CheckBox6 = True Then
    c(6) = 1
End If
If CheckBox7 = True Then
    c(7) = 1
End If

DateBefore = Weekday(EnteredDate)

If c(1) = 1 Or c(2) = 1 Or c(3) = 1 Or c(4) = 1 Or c(5) = 1 Or c(6) = 1 Or c(7) = 1 Then

    n = 1
    Do Until c(DateBefore + n) = 1
    
        If DateBefore + n > 7 Then
        
            DateBefore = -7 + DateBefore
        
        End If
        
        n = n + 1
    
    Loop
    
    DateAfter = EnteredDate + n

 

[Anthony47]

I understand that the user might check for more than a single option, and you already got a working solution (don't worry about "elegance": if it works, then it is perfect). Good.

If anyway you wish to dare your goodluck, you migth try this version:

Sub nextDay()
Dim StartDate, FinDate
Dim wMask As String
'
wMask = "1111111"

StartDate = Range("D1").Value                                                       '<<< Modify to reflect your start date
'Complete the If for all of your controls:                                        '<<<
If CheckBox1 Then Mid(wMask, 7, 1) = "0"        '7=Sun
If CheckBox2 Then Mid(wMask, 1, 1) = "0"        '1=Mon
If CheckBox3 Then Mid(wMask, 2, 1) = "0"        '2=Tue
'..
'..
'..
'..
If wMask = "1111111" Then wMask = "0000011"
FinDate = Application.WorksheetFunction.WorkDay_Intl(StartDate, 1, wMask)
'
'Now FinDate Contains the searched date, use it according your needs
'
End Sub

Just in case you are curious to try...

Bye