PDA

View Full Version : [SOLVED] Date keeps returning time, don't understand why



TatuMbili
10-11-2014, 02:21 PM
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.

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



Thanks all.

TatuMbili
10-11-2014, 05:47 PM
Figured it out when the lightbulb came on.


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

SamT
10-11-2014, 06:05 PM
I'll do it this time, but you can mark your threads "Solved" using the "Thread Tools" at the top of the thread.

TatuMbili
10-11-2014, 08:42 PM
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
10-11-2014, 09:50 PM
Thankyou TatuMbill and welcome to the forum

SamT
10-12-2014, 04:13 PM
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
10-12-2014, 04:35 PM
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
10-13-2014, 06:59 PM
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.