Sub RefreshDates(inDate, currDate)
'Automatically refreshes variable dates on Daily Update Worksheet
Worksheets("Daily Update").Activate
'Using Daily Update as the active sheet, all actions will occur on this worksheet
'unless otherwise stated
With ActiveSheet
Dim DayOfWeek As Integer
Dim NameOfMonth As String * 3
Dim MonthNum As Integer
Dim k As Integer
Cells(6, 2) = currDate - 1
'Determines date of weekday before
Cells(4, 7) = inDate
'Sets the Date at the top of the sheets
DayOfWeek = Weekday(inDate - 1, vbMonday)
Cells(6, 10) = inDate + 5 - DayOfWeek
Cells(6, 12) = inDate + 6 - DayOfWeek
'Determines the date of saturday and sunday
Cells(6, 4) = Format(inDate - DayOfWeek, "m/d/yy") & " Week"
Cells(6, 14) = "Week of " + Format(inDate - DayOfWeek, "m/d/yy")
'Formats the week so that vlookup works correctly
NameOfMonth = MonthName(Month(currDate - 1), True)
Cells(6, 6) = NameOfMonth & Year(currDate - 1)
Cells(6, 15) = NameOfMonth
'Formats the month name (appended to 3 letters)
Cells(6, 8) = Format(currDate - 1, "yyyy") & " Total"
End With
Worksheets("Graph Data").Activate
'Updating graph data sheet the day after the first weekday in the new month
'When using with ReportByDate, won't have any changes to the factor numbers
With ActiveSheet
If Cells(2, 1) <> Month(currDate - 1) Then
Dim MonthDays As Integer
Dim i As Integer
Dim aSheet As String
Dim aRange As String
Dim sacRange As Range
Dim sacFactor As Long
Dim bpFactor As Long
Rows("31:37").ClearContents
Cells(2, 1) = MonthName(Month(currDate - 1), True)
aSheet = Worksheets("2010 SAC.BP.Actual").Name
aRange = "A20:A31"
'for different year than 2010, range will need to change
Set sacRange = Worksheets(aSheet).Range(aRange).Find(What:=DateSerial(Year(currDate - 1), Month(currDate - 1), 1)) Cells(40, 16).Formula = "='2010 SAC.BP.Actual'!" + sacRange.Offset(0, 20).Address 'SAC factor
Cells(40, 17).Formula = "='2010 SAC.BP.Actual'!" + sacRange.Offset(0, 21).Address 'BP factor
MonthDays = Day(DateSerial(Year(currDate - 1), Month(currDate - 1) + 1, 1) - 1)
'Number of days in the month
i = 1 'day looping counter
Do While (i <= MonthDays)
Cells(i + 2, 1) = DateSerial(Year(currDate - 1), Month(currDate - 1), i)
If ((Weekday(Cells(i + 2, 1), vbMonday) = 6) + (Weekday(Cells(i + 2, 1), vbMonday) = 7)) Then
Cells(i + 2, 2) = 0.2
Cells(i + 2, 3) = 0.2
'Weekend factors
Else
Cells(i + 2, 2) = 0.8
Cells(i + 2, 3) = 0.7
'Weekday factors
End If
Cells(i + 2, 4).Formula = "=B" + CStr(i + 2) + "*D$" + CStr(MonthDays + 4)
Cells(i + 2, 6).Formula = "=C" + CStr(i + 2) + "*E$" + CStr(MonthDays + 4)
'Daily SAC and BP estimates
Cells(i + 2, 8).Formula = "=VLOOKUP('Graph Data'!A" + CStr(i + 2) + ",BudgetByDept!Y4:AS2012,21,FALSE)"
Cells(i + 2, 9).Formula = "=VLOOKUP('Graph Data'!A" + CStr(i + 2) + ",BudgetByDept!B4:X2012,21,FALSE)"
'BudgetByDept actuals and estimates
If i <> 1 Then
Cells(i + 2, 5).Formula = "=D" + CStr(i + 2) + "+E" + CStr(i + 1)
Cells(i + 2, 7).Formula = "=F" + CStr(i + 2) + "+G" + CStr(i + 1)
Cells(i + 2, 10).Formula = "=I" + CStr(i + 2) + "+J" + CStr(i + 1)
Cells(i + 2, 11).Formula = "=H" + CStr(i + 2) + "+K" + CStr(i + 1)
'Accumulated and MTDs
Else
Cells(i + 2, 5).Formula = "=D" + CStr(i + 2)
Cells(i + 2, 7).Formula = "=F" + CStr(i + 2)
Cells(i + 2, 10).Formula = "=I" + CStr(i + 2)
Cells(i + 2, 11).Formula = "=H" + CStr(i + 2)
'1st day of the month accumulated
End If
i = i + 1 'loop counter
Loop
Cells(i + 3, 2).Formula = "=SUM(B3:B" + CStr(i + 1) + ")" 'SAC factor
Cells(i + 3, 3).Formula = "=SUM(C3:C" + CStr(i + 1) + ")" 'BP factor
Cells(i + 3, 4).Formula = "=P40/B" + CStr(i + 3) 'SAC dose/factor
Cells(i + 3, 5).Formula = "=Q40/C" + CStr(i + 3) 'BP dose/factor
End If
End With
End Sub
The bolded portion is the portion that when in debug mode, instead of giving 1/1/2011, keeps giving me 11/1/2011 and I don't know why. I thought I fixed it about an hour ago, but at this point, I don't know what Excel is doing anymore. Like I said, I know it's vague, but if someone who has ran into a similar problem, I'd like to know where you began to LOOK to find out why it kept giving you the wrong output.
Bookmarks