CODEWITHSUNDEEP
vbaexceldate
Anthony
Anthony

Reputation: 552

With the code below I receive the error Variable not defined

Columns A, D and E are date and time.

I am trying to find out how many times the date in Column A falls between the start dates and end dates.

Column A may vary between 30 and 60 days while the start and end dates run to thousands.

Two questions:

  1. Why am I receiving error Variable Not defined with the code below?
  2. If cell A2 is date and time 24Feb17 12H00 then what formula do I put in cell A3 so that it reads 25Feb17 12H00 and so on?

Code:

Option Explicit
Sub DaysCount()
    Dim endRow As Long
    Dim LastRow As Long
    Dim ICount As Long
    Dim Day() As Variant
    Dim StartDate() As Variant
    Dim EndDate() As Variant
    ICount = 0
    With ThisWorkbook.Worksheets("sheet1")
        LastRow = .Range("A" & .Rows.count).End(xlUp).Row
        endRow = .Range("D" & .Rows.count).End(xlUp).Row
        Day = Sheet1.Range("A2:A" & LastRow)
        StartDate = Sheet1.Range("D2:D" & endRow)
        EndDate = Sheet1.Range("E2:E" & endRow)

        For i = LBound(StartDate) To UBound(StartDate)
            For J = LBound(Day) To UBound(Day)
                If Day(J, 1) >= StartDate(i, 1) And Day(J, 1) <= EndDate(i, 1) Then
                    ICount = ICount + 1
                Else

                End If
                Sheet1.Range("B" & J).Value = ICount
            Next i
            ICount = 0
        Next J
    End With
End Sub

Upvotes: 1

Views: 1711

Answers (1)

R3uK
R3uK

Reputation: 14547

  1. Option Explicit forces you to declare all variables, so you need to declare i and j too.
    And your Next i and Next j weren't in the good order!

  2. VBA : DateAdd("d",.Range("A3"),1)

Corrected code:

Option Explicit

Sub DaysCount()
Dim i As Long
Dim j As Long
Dim endRow As Long
Dim LastRow As Long
Dim ICount As Long
Dim Day() As Variant
Dim StartDate() As Variant
Dim EndDate() As Variant
ICount = 0
With ThisWorkbook.Worksheets("sheet1")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    endRow = .Range("D" & .Rows.Count).End(xlUp).Row
    Day = Sheet1.Range("A2:A" & LastRow)
    StartDate = Sheet1.Range("D2:D" & endRow)
    EndDate = Sheet1.Range("E2:E" & endRow)

    For i = LBound(Day) To UBound(Day)
        For j = LBound(StartDate) To UBound(StartDate)
            If Day(j, 1) >= StartDate(i, 1) And Day(j, 1) <= EndDate(i, 1) Then
                ICount = ICount + 1
            Else
            End If
        Next j
        .Range("B" & i).Value = ICount
        ICount = 0
    Next i
End With
End Sub

Upvotes: 2

Related Questions