Reducing the amount of code with Arrays

[gareth1979]

This is my code for checking the system clock and comparing it with the dates of the tax year

Sub dateesystem()
'decclare all variable to differnt type
'-------------------------------------------------
Dim StartDateMonth01 As Date
Dim StartDateMonth02 As Date
Dim StartDateMonth03 As Date
Dim StartDateMonth04 As Date
Dim StartDateMonth05 As Date
Dim StartDateMonth06 As Date
Dim StartDateMonth07 As Date
Dim StartDateMonth08 As Date
Dim StartDateMonth09 As Date
Dim StartDateMonth10 As Date
Dim StartDateMonth11 As Date
Dim StartDateMonth12 As Date
Dim EndDateMonth01 As Date
Dim EndDateMonth02 As Date
Dim EndDateMonth03 As Date
Dim EndDateMonth04 As Date
Dim EndDateMonth05 As Date
Dim EndDateMonth06 As Date
Dim EndDateMonth07 As Date
Dim EndDateMonth08 As Date
Dim EndDateMonth09 As Date
Dim EndDateMonth10 As Date
Dim EndDateMonth11 As Date
Dim EndDateMonth12 As Date
Dim o As Date
Dim counter As Integer
counter = 0
'---------------------------------------------------------
'declare variables source and location
'---------------------------------------------------------
StartDateMonth01 = Worksheets("Sheet1").Range("b3").Value
EndDateMonth01 = Worksheets("Sheet1").Range("c3").Value
StartDateMonth02 = Worksheets("Sheet1").Range("b4").Value
EndDateMonth02 = Worksheets("Sheet1").Range("c4").Value
StartDateMonth03 = Worksheets("Sheet1").Range("b5").Value
EndDateMonth03 = Worksheets("Sheet1").Range("c5").Value
StartDateMonth04 = Worksheets("Sheet1").Range("b6").Value
EndDateMonth04 = Worksheets("Sheet1").Range("c6").Value
StartDateMonth05 = Worksheets("Sheet1").Range("b7").Value
EndDateMonth05 = Worksheets("Sheet1").Range("c7").Value
StartDateMonth06 = Worksheets("Sheet1").Range("b8").Value
EndDateMonth06 = Worksheets("Sheet1").Range("c8").Value
StartDateMonth07 = Worksheets("Sheet1").Range("b9").Value
EndDateMonth07 = Worksheets("Sheet1").Range("c9").Value
StartDateMonth08 = Worksheets("Sheet1").Range("b10").Value
EndDateMonth08 = Worksheets("Sheet1").Range("c10").Value
StartDateMonth09 = Worksheets("Sheet1").Range("b11").Value
EndDateMonth09 = Worksheets("Sheet1").Range("c11").Value
StartDateMonth10 = Worksheets("Sheet1").Range("b12").Value
EndDateMonth10 = Worksheets("Sheet1").Range("c12").Value
StartDateMonth11 = Worksheets("Sheet1").Range("b13").Value
EndDateMonth11 = Worksheets("Sheet1").Range("c13").Value
StartDateMonth12 = Worksheets("Sheet1").Range("b14").Value
EndDateMonth12 = Worksheets("Sheet1").Range("c14").Value
o = Now()
'----------------------------------------------------------
'If true or false
'----------------------------------------------------------
If (o >= StartDateMonth01 And o <= EndDateMonth01) Then
counter = 1
MsgBox counter
Else
If (o >= StartDateMonth02 And o <= EndDateMonth02) Then
counter = 2
MsgBox counter
Else
If (o >= StartDateMonth03 And o <= EndDateMonth03) Then
counter = 3
MsgBox counter
Else
If (o >= StartDateMonth04 And o <= EndDateMonth04) Then
counter = 4
MsgBox counter
If (o >= StartDateMonth05 And o <= EndDateMonth05) Then
counter = 5
MsgBox counter
Else
If (o >= StartDateMonth06 And o <= EndDateMonth06) Then
counter = 6
MsgBox counter
Else
If (o >= StartDateMonth07 And o <= EndDateMonth07) Then
counter = 7
MsgBox counter
Else
If (o >= StartDateMonth08 And o <= EndDateMonth08) Then
counter = 8
MsgBox counter
If (o >= StartDateMonth09 And o <= EndDateMonth09) Then
counter = 9
MsgBox counter
Else
If (o >= StartDateMonth10 And o <= EndDateMonth10) Then
counter = 10
MsgBox counter
Else
If (o >= StartDateMonth11 And o <= EndDateMonth11) Then
counter = 11
MsgBox counter
Else
If (o >= StartDateMonth12 And o <= EndDateMonth12) Then
counter = 12
MsgBox counter
Else
MsgBox "its not working"
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

However I want to try and reduce the script by using and array and creating a loop to do check the system date that way, below is what I have tried doing however I can seem to get anywhere can someone help or have any suggestions please

Sub chDates()
Dim myDatestart As Variant
Dim myDateEnd As Variant
Dim c As Long
Dim i As Variant
myDatestart = Range("a1:a5").Value
myDateEnd = Range("b1:b5").Value
 i = Now()
 c = 0
 While i >= ((UBound(myDatestart) And i >= (UBound(myDateEnd))))
 
 c = c + 1
 If i >= ((UBound(myDatestart) And i <= (UBound(myDateEnd)))) Then
 If c = c < 6 Then
 MsgBox c
Else
 End If
 End If
 
Wend
End Sub

 

[mse330]

Try the below

Sub Date_System()

Dim StartDate As Date, EndDate As Date, Counter As Integer, lRow As Long, Ws As Worksheet

Set Ws = Worksheets("Sheet1")
lRow = Ws.Range("B" & Rows.Count).End(xlUp).Row

For x = 2 To lRow
    StartDate = Ws.Cells(x, 2): EndDate = Ws.Cells(x, 3)
    If Date >= StartDate And Date <= EndDate Then
        Counter = x - 1
        MsgBox Counter
    End If
Next x

End Sub

 

[Fluff]

Another option

Sub chDates()
   Dim myDates As Variant
   Dim Mnth As Long
   Dim i As Long, j As Long
   myDates = Range("B3:C14").Value
   For i = 1 To UBound(myDates)
      If Date >= myDates(i, 1) And Date <= myDates(i, 2) Then
         Mnth = i
         Exit For
      End If
   Next i
   MsgBox Mnth
End Sub

 

[gareth1979]

Thanks mse200, however this doesn't seem to work

Here is my excel spread sheet

---

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]01/06/2018[/TD]
[TD]30/06/2018[/TD]
[/TR]
[TR]
[TD]01/07/2018[/TD]
[TD]30/07/2018[/TD]
[/TR]
[TR]
[TD]01/08/2018[/TD]
[TD]30/08/2018[/TD]
[/TR]
[TR]
[TD]01/09/2018[/TD]
[TD]30/09/2018[/TD]
[/TR]
</tbody>[/TABLE]


I want an array to collect the dates in column A1 - A5 and another Array to collect the dates B1-B5, then a loop will check the computer date against each of these array dates and if the value is true then message box should display the month of the tax year and if not carry on till it does, hope this helps (please be advised these dates and just random at the moment )

 

[gareth1979]

Another option

Sub chDates()
   Dim myDates As Variant
   Dim Mnth As Long
   Dim i As Long, j As Long
   myDates = Range("B3:C14").Value
   For i = 1 To UBound(myDates)
      If Date >= myDates(i, 1) And Date <= myDates(i, 2) Then
         Mnth = i
         Exit For
      End If
   Next i
   MsgBox Mnth
End Sub

Thanks fluff,Tried this and it doesn't seen to work

 

[Fluff]

I based that on your original code so it's looking in B3:C14 for dates set like this

Excel 2013 32 bit
	A	B	C
1
2
3		01/01/2018	31/01/2018
4		01/02/2018	28/02/2018
5		01/03/2018	31/03/2018
6		01/04/2018	30/04/2018
7		01/05/2018	31/05/2018
8		01/06/2018	30/06/2018
9		01/07/2018	31/07/2018
10		01/08/2018	31/08/2018
11		01/09/2018	30/09/2018
12		01/10/2018	31/10/2018
13		01/11/2018	30/11/2018
14		01/12/2018	31/12/2018
Sheet1

 

[gareth1979]

sorry fluff - I didn't setup my sheet the same as yours and it does work many thanks

 

[Fluff]

Glad we could help & thanks for the feedback