VBA Auto Fill Table with all the dates within a date range with inputed data.

[Nathan95]

Hey All,

Im pretty new too VBA so I have no clue on how to go about in doing this, But what Im looking to do is work on a leave tracker, where someone can input the start and end date range for the leave as well as what the leave type is and their Employee number (P Number). Then when they click the "Input Leave" Button I want it to auto fill in a table with each date within that date range to have a seperate row with the same Leave type and employee number that was entered.

Any help would be appreciated

 

[Nathan95]

If you mean the date just as a number like in your earlier image, then try adding this line

Sub Enter_Data()
  Dim FirstDate As Date, LastDate As Date
  Dim rBlanks As Range
 
  On Error Resume Next
  Set rBlanks = Range("K7,M7,K9,N9").SpecialCells(xlBlanks)
  On Error GoTo 0
  If rBlanks Is Nothing Then
    FirstDate = Range("K7").Value
    LastDate = Range("N7").Value
    With Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(LastDate - FirstDate + 1)
      .Value = Evaluate("Row(" & CLng(FirstDate) & ":" & CLng(LastDate) & ")")
      .NumberFormat = "dd-mmm-yy"
      .Offset(, 1).Value = Range("N9").Value
      .Offset(, -2).Value = Range("K9").Value
      .Offset(, -3).Value = Evaluate(.Offset(, -2).Address & "&" & .Address)
    End With
  Else
    MsgBox "Data missing"
  End If
End Sub

Hey Peter,

Another quick one, So i ended up using a user form and everything works perfectly. The only issue is to select the start and end date I called upon the Calendar Function in VBA, which works fine on my pc, However if i send the file to another user, since they don't have the calendar function enabled the calendar does not populate and instead they just get a black form (inplace of the calendar).

Is there a way to automatically call upon and assign the calendar function to each user when they press the button (at the start of the code) that way the calendar will appear for them?

 

[Peter_SSs]

Another quick one,

I normally fear "quick one" questions as they are often far from that.
However, in this case it is a quick one for me: Your question is not in my 'strong point' fields so I am not going to attempt to answer.

Since this is quite a different question to the original, I suggest that you start a new thread about this issue.
If you do, post back here with a link to the new thread. At that point I will lock this thread so we don't have more than one thread with the same question.

 

[Peter_SSs]

If you do, post back here with a link to the new thread.

Looks like you forgot about this.
New question re Calendar on UserForm is here: User Form Pop up Calendar Function
This thread now closed.