Date keeps returning time, don't understand why

[TatuMbili]

Hello all, VBA newbie here and thanks in advance. I've got a series of worksheets with a date at E3 on each sheet. My VBA code is for CompareMonth from 1 to 12, for each worksheet, see if the month of the date in E3 matches CompareMonth. If yes, copy the value in cell F29 and F30 to two values, G and P, else next worksheet, then next CompareMonth. My problem is that the value of the date that gets returned on each worksheet is 12:00:00 AM so my code never gets to yes, values G and P never get added. Any clues for me? I have put in MsgBox's so I get some visual clue as to what is happening: ActualDate keeps giving a value of 12 and then there is no value for G or P. Sorry it's so long, I don't know what I'm doing wrong, can't find anything in reference manuals or online as to why I can't get month out of the date value.

[VBA]Dim skw As Integer
Dim CompareMonth As Integer
Dim w As Integer
Dim G As Currency
Dim P As Currency
Dim FindDate As Date
Dim ActualDate As Integer
Dim testDate As Date
G = 0
P = 0
CompareMonth = 1
w = 2
skw = Worksheets.Count
MsgBox (skw)
For CompareMonth = 1 To 12
For w = 2 To skw
MsgBox ("skw is ") & skw 'this works
Worksheets(w).Activate
MsgBox ("date is ") & Range("E3").Value 'gives a blank here
testDate = Cells(5, 3).Value
testDate = Format(testDate, "dd/mm/yyyy")
FindDate = DateValue(testDate)
MsgBox (FindDate)
ActualDate = DatePart("m", FindDate)
MsgBox (w) & " " & ActualDate 'ActualDate gives 12 always
If CompareMonth = ActualDate Then
MsgBox "The month: " & ActualDate 'only reach here when CompareMonth is 12
G = G + Range("F29").Value
P = P + Range("F30").Value
Else
MsgBox ("Nothing")
End If
Next w
Worksheets(1).Select
Cells(CompareMonth + 2, 2).Value = G
Cells(CompareMonth + 2, 3).Value = P
MsgBox (CompareMonth)
G = 0
P = 0
Next CompareMonth
MsgBox ("Done")
End Sub
[/VBA]


Thanks all.

[TatuMbili]

Figured it out when the lightbulb came on.

[VBA]
Private Sub CommandButton7_Click()
On Error Resume Next
Dim skw As Integer
Dim CompareMonth As Integer
Dim w As Integer
Dim GST As Currency
Dim PST As Currency
Dim FindDate As Date
Dim ActualDate As Integer
Dim testDate As Date
GST = 0
PST = 0
CompareMonth = 1
w = 2
skw = Worksheets.Count
'MsgBox (skw)
For CompareMonth = 1 To 12
GST = 0
PST = 0
For w = 2 To skw
'MsgBox ("skw is ") & skw
Worksheets(w).Activate
'MsgBox ("date is ") & ActiveSheet.Range("E3")
testDate = ActiveSheet.Range("E3")
testDate = Format(testDate, "dd/mm/yyyy")
FindDate = DateValue(testDate)
'MsgBox (FindDate)
ActualDate = DatePart("m", FindDate)
'MsgBox (w) & " " & ActualDate
'If Month("E3").Value = m Then
If CompareMonth = ActualDate Then 'DatePart("m", FindDate) Then
'MsgBox "The month: " & ActualDate 'DatePart("m", FindDate)
GST = GST + ActiveSheet.Range("F29").Value
PST = PST + ActiveSheet.Range("F30").Value
Else
'MsgBox ("Nothing")
End If
'MsgBox (GST)
Next w
Worksheets(1).Select
ActiveSheet.Cells(CompareMonth + 2, 2).Value = GST
ActiveSheet.Cells(CompareMonth + 2, 3).Value = PST
'MsgBox (CompareMonth)
Next CompareMonth
MsgBox ("Done")
End Sub
[/VBA]

[SamT]

I'll do it this time, but you can mark your threads "Solved" using the "Thread Tools" at the top of the thread.

[TatuMbili]

I was on my way back to the site to mark this as "Solved" as I thought everything was good then realized I also have to do all this in a fiscal year format. Fiscal year starts October 1 so my first sheet's initial cell for data Cells(CompareMonth + 2, 2) should be October's value, not January's. The first sheet three columns: Month, GST Totals, PST Totals. I want the first month totals to be for October 2014, last is September 2015. All the additional worksheets that will be added will be within the fiscal year so anything with month 10 will be for October 2014, anything for September in 2015, etc. How do I re-order the calculation? It is so easy(in hindsight lol) with For CompareMonth = 1 to 12 and then placing them on the first sheet in Cells(CompareMonth +2, 2).

Thank you for any hints/suggestions, including if I should mark this as Solved and then start a new thread.

[Aussiebear]

Thankyou TatuMbill and welcome to the forum

[SamT]

Private Sub CommandButton7_Click()
    Dim WkSht As Long 'Worksheets(Index number)
    Dim GST As Currency
    Dim PST As Currency
    Dim CompareMonth As Long
    
    For CompareMonth = 1 To 12
      GST = 0
      PST = 0

      For WkSht = 2 To Worksheets.Count
        With Worksheets(WkSht)
          If Month(.Range("E3")) = CompareMonth Then
            GST = GST + .Range("F29").Value
            PST = PST + .Range("F30").Value
          End If
         End With
       Next WkSht

      With Worksheets(1).Rows(FiscalRow(CompareMonth))
        .Cells(2).Value = GST 'Second Cell (Column) in Row
        .Cells(3).Value = PST
      End With
    Next CompareMonth

End Sub

Private Function FiscalRow(CompareMonth As Long) As Long
Select Case CompareMonth
  Case 1: FiscalRow = 6 'Case 1 = January. 6 = Row 6. Adjust as needed.
  Case 2: FiscalRow = 7
  Case 3: FiscalRow = 8
  Case 4: FiscalRow = 9
  Case 5: FiscalRow = 10
  Case 6: FiscalRow = 11
  Case 7: FiscalRow = 12
  Case 8: FiscalRow = 13
  Case 9: FiscalRow = 14
  Case 10: FiscalRow = 3 'Case 10 = October
  Case 11: FiscalRow = 4
  Case 12: FiscalRow = 5
End Select
End Function

[SamT]

Alternate method, assumes Cel value is month in format "Jan" or "January."
IF Cel Value is month and year, then replace "Month(Cel & " 14, 1999")" with "Month(Cel)"

Option Explicit

Private Sub CommandButton7_Click()
    Dim WkSht As Long 'Worksheets(Index number)
    Dim GST As Currency
    Dim PST As Currency
    Dim Cel As Range
    
    For Each Cel In Worksheets(1).Range("A3:A14")

      For WkSht = 2 To Worksheets.Count
        With Worksheets(WkSht)
          If Month(.Range("E3")) = Month(Cel & " 14, 1999") Then
            GST = GST + .Range("F29").Value
            PST = PST + .Range("F30").Value
          End If
        End With
      Next WkSht

      Cel.Offset(, 1).Value = GST: GST = 0 'Colon = "Next Line"
      Cel.Offset(, 2).Value = PST: PST = 0
    Next Cel

End Sub

[TatuMbili]

Thanks, SamT, worked like a charm! I realize I was making my life waaayyyy more complicated than it needed to be, will look for the cleanest way to do things.