Difficulity identifying the month in a range then proceeding with a calcualtion.

[kevin8482]

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

		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.

<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>

 

[kevin8482]

Has anyone had an opportunity to review this? I am grateful for any help I can get please.