CODEWITHSUNDEEP
excelvba
Lorenzo Castagno
Lorenzo Castagno

Reputation: 582

Select a fixed columns in a VBA

I need help with this VBA. Thanks in advance.

I want to select a range (of values, the whole column) for the values: FirstDate, EndDate and Number. My VBA:

Sub DateTest()
    Dim FirstDate As Date    ' Declare variables.
    Dim IntervalType As String
    Dim Number As Integer
    Dim EndDate As Date
    Dim TempDate As Date
    Dim i As Integer

    IntervalType = "m" ' "m" specifies MONTHS as interval.
    FirstDate = Cells(1, 1).Value
    EndDate = Cells(1, 2).Value
    Number = Cells(1, 3).Value  ' "Number" For the syntax DateAdd.

    ' If the number is not greater than zero an infinite loop will happen.
    If Number <= 0 Then
        MsgBox "Number needs to be greater than 0", vbCritical
        Exit Sub
    End If

    i = 1
    Do Until TempDate = EndDate
       If i <= 1 Then
           TempDate = DateAdd(IntervalType, Number, FirstDate)
       Else
           TempDate = DateAdd(IntervalType, Number, TempDate)
        End If
        i = i + 1
        Debug.Print i
    Loop
    Range("D1").Value = i - 1
End Sub

As I wrote before I want to run my Macro not only for the 1st cell (currently the macro works fine for the value (1,1) (1,2) (1,3)), as you can see above for FirstDate, EndDate and Number I wan to use for all dates in: Column1, Column2, Column3

I made some changes, for example for EndDate:

EndDate = Format(.Cells(lRow, 2).Value)

is not working, take the vale for 1 cell and the rest of the values in the column are ignored.

I tried: 

    FirstDate = Range("A1:A20").Select

    EndDate = Range("B1:B20").Select

    Number = Range("C1:C20").Select

But I get: "Number needs to be greater than 0"

Upvotes: 1

Views: 736

Answers (2)

Lorenzo Castagno
Lorenzo Castagno

Reputation: 582

solved, see below: 

 Sub DateTest()
    Dim FirstDate As Date    ' Declare variables.
    Dim IntervalType As String
    Dim Number As Integer
    Dim EndDate As Date
    Dim TempDate As Date
    Dim i As Integer

    IntervalType = "m" ' "m" specifies MONTHS as interval.

    With ActiveWorkbook.Worksheets(1)
    lLastRow = .UsedRange.Rows.Count

    For lRow = 1 To lLastRow

    FirstDate = Format(.Cells(lRow, 1).Value, "YYYY-MM-DD")
    EndDate = Format(.Cells(lRow, 2).Value, "YYYY-MM-DD")
    Number = .Cells(lRow, 3).Value

    ' If the number is not greater than zero an infinite loop will happen.
    If Number <= 0 Then
        MsgBox "Number needs to be greater than 0", vbCritical
        Exit Sub
    End If

    i = 1
    Do Until TempDate = EndDate
       If i <= 1 Then
           TempDate = DateAdd(IntervalType, Number, FirstDate)
       Else
           TempDate = DateAdd(IntervalType, Number, TempDate)
        End If
        i = i + 1
        Debug.Print i
    Loop
    Cells(lRow, 4).Value = i - 1
    'Range("D1").Value = i - 1

      Next
      End With

    End Sub

Upvotes: 0

Vityata
Vityata

Reputation: 43585

There are probably 2 issues in the code:

  • Reading from Excel
  • Business Logic

Concerning the reading from the worksheets, make sure that the parent worksheet is always mentioned. E.g., instead of writing FirstDate = Cells(1, 1).Value write FirstDate = Worksheets("DatesWorksheetName")Cells(1, 1).Value. Furthermore, this is a must-read topic for VBA - How to avoid using Select in Excel VBA.

For the business logic, replace the value read from the worksheet with some standard values and give it a try until it works:

Sub TestMe()

    Dim tempDate As Date
    Dim endDate As Date
    Dim firstDate As Date

    firstDate = #5/19/2019#
    endDate = #11/19/2019#
    tempDate = #8/19/2019#

    Dim i As Long: i = 1
    Dim intervalType As String: intervalType = "m"
    Dim number As Long: number = 5

    Do Until tempDate >= endDate
        If i <= 1 Then
           tempDate = DateAdd(intervalType, number, firstDate)
        Else
           tempDate = DateAdd(intervalType, number, tempDate)
        End If
        i = i + 1
        Debug.Print i
    Loop

End Sub

Upvotes: 1

Related Questions