Excel: Enter dates in specific column

Question

My requirement is:

I want to enter date from 1st Jan to 31st Jan in columns E5 to AI5. Currently using the below code which is not working.

Secondly year i m taking as user input which should change every time.

Sub LoopA()
    Call Using_InputBox_Method
    Dim i As Integer
    Dim j As Integer
    Dim PH As Integer
    i = 5
    For j = 5 To 35
               Cells(i, j).Value = "=Date(E1,1,j)"
    Next j


End Sub

    Public Function Using_InputBox_Method() As Integer


      Dim Response As Integer

  ' Run the Input Box.
  Response = Application.InputBox("Enter a Year.", _
     "Number Entry", , 250, 75, "", , 1)

  ' Check to see if Cancel was pressed.
  If Response <> False Then

     ' If not, write the number to the first cell in the first sheet.
     Worksheets(1).Range("E1").Value = Response

  End If

   Using_InputBox_Method = Response

End Function

score 0 · Answer 1 · edited May 23 '17 at 12:14

A)

Anything within " will be considered as a String. So "=Date(E1,1,j)" is just a string. What you want, I guess is

"=Date(E1,1," & j & ")"

B)

For j = 5 To 35

Are you sure you want to go up till 35? The max you can have in any month is 31 :)

Syntax of =Date() is DATE(year,month,day)

Also you would need an additional check here to see if it is a valid date. For example 30th Feb will give you an error.

C)

InputBox should be avoided to accept dates. It can generate errors. You may want to use THIS. If you still want to use InputBox then you will have to do validations to ensure that there are no errors.

D)

Regarding, the Year changing automatically, You will have to increment the Year in Column E once the user automatically enters the date.

Is this what you are trying?

Sub Sample()
    Dim Yr As Long, i As Long
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("Sheet1")

    Yr = Application.InputBox("Enter a Year.", _
     "Number Entry", , 250, 75, "", , 1)

    '~~> Set it to whatever Year Range you want
    If Yr < 1900 Or Yr > 9999 Then
        MsgBox "Incorrect Year"
        Exit Sub
    End If

    With ws
        .Range("E1").Value = Yr

        For i = 5 To 35
            .Cells(5, i).Formula = "=Date(E1,1," & (i - 4) & ")"
        Next i
    End With
End Sub

Excel: Enter dates in specific column

1 Answers1