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

kevin8482

New Member
Joined
Sep 3, 2014
Messages
2
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 TIMEPART TIMESCHOOL AGE **
(21 hours of more)(20 hours or less)
1DAYS23135
HOURS13811592
22 MONTHS462610
276230184
33 MONTHS693915
414345276
44 MONTHS925220
552460368
55 MONTHS1156525
690575460
66 MONTHS1387830
828690552
77 MONTHS1619135
966805644
88 MONTHS18410440
1104920736
99 MONTHS20711745
14241035828
1010 MONTHS23013050
13801150920
1111 MONTHS25314355
151812651012
1212 MONTHS27615660
165613801104
In Home Care19486--------
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>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

kevin8482

New Member
Joined
Sep 3, 2014
Messages
2
Has anyone had an opportunity to review this? I am grateful for any help I can get please.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,102
Messages
5,640,119
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top