Select a fixed columns in a VBA

Question

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"

Have you looked up how to apply [`.Format`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications)? Besides that, with your second attempt you'r trying to select a range while applying values to some variables at the same time? I think what you are trying to do is to loop through a range. Have a look around on internet and so. I bet there are some usefull examples to be found. — JvdV, Aug 19 '19 at 12:28

score 1 · Answer 1 · answered Aug 19 '19 at 12:42

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

Hi Vityata thanks for your time, see I added FirstDate = Worksheets("Sheet1").Cells(1, 1).Value EndDate = Worksheets("Sheet1").Cells(1, 2).Value Number = Worksheets("Sheet1").Cells(1, 3).Value I get Run time error '5' invalid procedure call or agument. — Lorenzo Castagno, Aug 19 '19 at 15:39
@Lorenzo Castagno - I am afk now, but did you do the replacement in my code? Then it should work, I guess. Just to make sure - "Cells(1,3)" is "C1" and not "A3". — Vityata, Aug 19 '19 at 18:01

score 0 · Accepted Answer · answered Aug 20 '19 at 09:50

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

Select a fixed columns in a VBA

2 Answers2