Calculating dates

[WayneAli]

Hi all,
Finally getting somewhere with my data sheet but have come unstuck with calculating dates in VBA.

I have a UserForm set up that the user can enter a "New User" name and then go through a series of 55 text boxes and enter the days remaining before they need to complete individual online eLearning modules.

I'll pop the code that I have then give a bit of an explanation.

Dim ws As Worksheet
Dim crew_Name As Variant, r As Variant, dr As Variant
Dim i As Integer

    Set ws = ThisWorkbook.Worksheets("DataSheet1")
'------Set crew_Name as Surname,  Firstname
    crew_Name = Me.TextBox101 & ",  " & Me.TextBox100
        If Len(crew_Name) = 0 Then Exit Sub
'------Find the current row for this staff member, previous code has entered them to the end of column 2 on DataSheet1
    r = Application.Match(crew_Name, ws.Range("A3:BB22").Columns(2), 0)
        If IsError(r) Then Err.Raise 744, , crew_Name & " Not Found"
'------Subtracts the number of days in TextBox & i from todays date
    dr = DateAdd("d", - Val("Me.TextBox & i"), Date)

'------Row 2 in the spread sheet has a time interval identifying how often the eLearn needs completing, 6, 12 or 24 monthly
'         the idea is that a search is made of row 2 to find either 6, 12 or 24 then adds either 6, 12 or 24 months to "dr"
'         the resulting date is entered into the corresponding staff name row in the respective column i
    If ws.Range("A1:BA22").Cells(2, i).Value = 6 Then
        ws.Range("A3:BB22").Cells(CLng(r), i).Value = DateAdd("m", 6, dr)
    ElseIf ws.Range("A1:BA22").Cells(2, i).Value = 12 Then
        ws.Range("A3:BB22").Cells(CLng(r), i).Value = DateAdd("m", 12, dr)
    ElseIf ws.Range("A1:BA22").Cells(2, i).Value = 24 Then
        ws.Range("A3:BB22").Cells(CLng(r), i).Value = DateAdd("m", 24, dr)
'------If an entry has not been made the cell in the speadsheet column i is left blank
    ElseIf "me.textbox" & i = "" Then
        ws.Range("A3:BB22").Cells(CLng(r), i).Value = ""
End If
Next i

I feel I am close but it is entering the "New crew_Name" and referencing the row of that name, however the date calculation is going awry. Instead of adding 6, 12 or 24 months to "dr", in essence this should enter a date in the speadsheet when the crew_Name last complete the respective course, what it is doing is just adding 6, 12 or 24 months onto todays date (depending on row 2) into the corresponding crew name row.

The other thing I have noticed is that it is making an entry in every column/cell of the crew_Name irrespective of whether the "TextBox & i" is filled or blank, the code is identifying the 6, 12 or 24 in row 2 of the sheet and entering a date into the crew_Name row.

I've spent the day on this and this is probably the closest I've come to success, but I'm knackered so thought I'd ask here.

Hopefully this all makes sense, and if anyone can see how I need to tweek my code so that the result entered into the respective row is "Today - TextBox & i + '6, 12 or 24 months' or '182, 365 or 730' days" that would be fantastic.

Thanks all

 

[Anthony47]

I guess this line is wrong:
dr = DateAdd("d", - Val("Me.TextBox & i"), Date)

Maybe:

dr = DateAdd("d", - Val(Me.TextBox & i), Date)

Beware that a For I=xx to yy is missing somewhere in the code

 

[WayneAli]

Beware that a For I=xx to yy is missing somewhere in the code

Thanks Anthony, I'll give that a try. The "For i = 1 To 55" is a little further up in the code