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
<colgroup><col width="64" style="width: 48pt; mso-width-source: userset; mso-width-alt: 2340;">
<col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;">
<col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;">
<col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;">
<col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;">
<tbody>
</tbody>
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) | |||
1 | DAYS | 23 | 13 | 5 |
HOURS | 138 | 115 | 92 | |
2 | 2 MONTHS | 46 | 26 | 10 |
276 | 230 | 184 | ||
3 | 3 MONTHS | 69 | 39 | 15 |
414 | 345 | 276 | ||
4 | 4 MONTHS | 92 | 52 | 20 |
552 | 460 | 368 | ||
5 | 5 MONTHS | 115 | 65 | 25 |
690 | 575 | 460 | ||
6 | 6 MONTHS | 138 | 78 | 30 |
828 | 690 | 552 | ||
7 | 7 MONTHS | 161 | 91 | 35 |
966 | 805 | 644 | ||
8 | 8 MONTHS | 184 | 104 | 40 |
1104 | 920 | 736 | ||
9 | 9 MONTHS | 207 | 117 | 45 |
1424 | 1035 | 828 | ||
10 | 10 MONTHS | 230 | 130 | 50 |
1380 | 1150 | 920 | ||
11 | 11 MONTHS | 253 | 143 | 55 |
1518 | 1265 | 1012 | ||
12 | 12 MONTHS | 276 | 156 | 60 |
1656 | 1380 | 1104 | ||
In Home Care | 194 | 86 | -------- | |
Hours | ||||
**School Age: If authorization months cover June, July and/or August use 23 days and 138 hours for each month the authorization is needed for full-time care. |