[vba] find next day

Antman404

New Member
Joined
May 26, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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"
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,267
I would use RadioButtons (OptionButtons), that are mutually exclusive, rather than CheckBoxes

Anyway, something like
VBA Code:
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

New Member
Joined
May 26, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,267
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:
VBA Code:
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
 

Forum statistics

Threads
1,144,389
Messages
5,724,073
Members
422,534
Latest member
ThiccNugg

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
Top