# [vba] find next day

#### Antman404

##### New Member
Hello all you helping ones.

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
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

#### Antman404

##### New Member
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
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
'

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
'..
'..
'..
'..
'
'Now FinDate Contains the searched date, use it according your needs
'
End Sub``````
Just in case you are curious to try...

Bye

Replies
1
Views
81
Replies
1
Views
102
Replies
5
Views
126
Replies
6
Views
268
Replies
5
Views
302

### Forum statistics

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.

### Which adblocker are you using?    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

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