I am fairly new to excel VBA and I am stuck so far. I am attempting to write some code for use with a userform. The logic I would like to use is as follows:
If the calculated number of months = A:A then use the days/hours from Column E, however if any of the months are 6,7,or 8 (June, July or August) then I need to know how many months meet that criteria and use the day/hours from column C for those months + the days/hours from column E for the remaining calculated months. I have included the table below and the code I am currently using. Thanks in advance!
Sub testing()
Dim Start1, Start2, Start3, Start4, End1, End2, End3, End4
Start1 = "6 / 1 / 14"
'Start1 = frmMainPage.Label5.Caption
'Start2 = frmMainPage.Label6.Caption
'Start3 = frmMainPage.Label7.Caption
'Start4 = frmMainPage.Label8.Caption
End1 = "6 / 30 / 14"
'End1 = frmMainPage.Label11.Caption
'End2 = frmMainPage.Label12.Caption
'End3 = frmMainPage.Label13.Caption
'End4 = frmMainPage.Label14.Caption
'MsgBox (Month(End3))
If Start1 = "Select Start Date" Then
MsgBox ("Please select a start date!")
GoTo 2
ElseIf End1 = "Select Start Date" Then
MsgBox ("Please select an end date!")
GoTo 2
Else
GoTo 3
End If
3
'Child 1
Dim dBeginDate As Date
Dim dEndDate As Date
Dim dDate As Date
Dim intMonths, intCalcMth As Integer
' Beginning date.
dBeginDate = DateValue(Start1)
' Ending Date.
dEndDate = DateValue(End1)
' Calculate number of months between dates.
intMonths = ((Year(dEndDate) - Year(dBeginDate)) * 12) + _
Month(dEndDate) - Month(dBeginDate)
' Display number of months.
'MsgBox Str$(intMonths) & " month(s)"
' display number of months of authorizations based on calcualtion
intCalcMth = ((dEndDate - dBeginDate) / 30)
'MsgBox " This authorization is for " & Str$(Round(intCalcMth)) & " month(s)"
'Finds the hours for authorizations
If (Round(intCalcMth)) = Sheet2.Range("A3").Value Then
'checks for summer months
For dDate = dBeginDate To dEndDate
If Format(dDate, "dd") = "01" Then
If Format(dDate, "m") >= 6 And Format(dDate, "m") <= 8 Then
MsgBox ("Summer Months")
End If
End If
Next dDate
Sheet2.Activate
Sheet2.Range("A3").Select
ActiveCell.Offset(0, 4).Select
Days = ActiveCell.Value
hours = ActiveCell.Offset(1, 0).Value
frmMainPage.TextBox1.Value = Str$(Days)
frmMainPage.TextBox8.Value = Str$(hours)
end if
end sub
If the calculated number of months = A:A then use the days/hours from Column E, however if any of the months are 6,7,or 8 (June, July or August) then I need to know how many months meet that criteria and use the day/hours from column C for those months + the days/hours from column E for the remaining calculated months. I have included the table below and the code I am currently using. Thanks in advance!
Sub testing()
Dim Start1, Start2, Start3, Start4, End1, End2, End3, End4
Start1 = "6 / 1 / 14"
'Start1 = frmMainPage.Label5.Caption
'Start2 = frmMainPage.Label6.Caption
'Start3 = frmMainPage.Label7.Caption
'Start4 = frmMainPage.Label8.Caption
End1 = "6 / 30 / 14"
'End1 = frmMainPage.Label11.Caption
'End2 = frmMainPage.Label12.Caption
'End3 = frmMainPage.Label13.Caption
'End4 = frmMainPage.Label14.Caption
'MsgBox (Month(End3))
If Start1 = "Select Start Date" Then
MsgBox ("Please select a start date!")
GoTo 2
ElseIf End1 = "Select Start Date" Then
MsgBox ("Please select an end date!")
GoTo 2
Else
GoTo 3
End If
3
'Child 1
Dim dBeginDate As Date
Dim dEndDate As Date
Dim dDate As Date
Dim intMonths, intCalcMth As Integer
' Beginning date.
dBeginDate = DateValue(Start1)
' Ending Date.
dEndDate = DateValue(End1)
' Calculate number of months between dates.
intMonths = ((Year(dEndDate) - Year(dBeginDate)) * 12) + _
Month(dEndDate) - Month(dBeginDate)
' Display number of months.
'MsgBox Str$(intMonths) & " month(s)"
' display number of months of authorizations based on calcualtion
intCalcMth = ((dEndDate - dBeginDate) / 30)
'MsgBox " This authorization is for " & Str$(Round(intCalcMth)) & " month(s)"
'Finds the hours for authorizations
If (Round(intCalcMth)) = Sheet2.Range("A3").Value Then
'checks for summer months
For dDate = dBeginDate To dEndDate
If Format(dDate, "dd") = "01" Then
If Format(dDate, "m") >= 6 And Format(dDate, "m") <= 8 Then
MsgBox ("Summer Months")
End If
End If
Next dDate
Sheet2.Activate
Sheet2.Range("A3").Select
ActiveCell.Offset(0, 4).Select
Days = ActiveCell.Value
hours = ActiveCell.Offset(1, 0).Value
frmMainPage.TextBox1.Value = Str$(Days)
frmMainPage.TextBox8.Value = Str$(hours)
end if
end sub
FULL TIME | PART TIME | SCHOOL AGE ** | ||
(21 hours of more) | (20 hours or less) |