CODEWITHSUNDEEP
vbaexcel
Heroz
Heroz

Reputation: 21

Total monthly values excel VBA

Ive searched for several hours and since my knowledge of VBA is very limited I'm at a loss. I have an "Inventory Total" for several different items going across the top row (Row B) which is updated by imputing a positive or negative amount on a line below it along with the date of update at the far left (column 1). I have another book that needs to keep track of only the subtractions made to each field by the month.

I already have an (auto_run) macro, so I plan to add this to it. It needs to check today's date and if its the first of the month take the total of all negative numbers (per column) for THAT MONTH ONLY and add it to the top line of the other book.

The part I need to know is how to get the totals per month in VBA.

This is what I'm using to to check today's date:

If Date - Day(Date) + 1 = Date - Day(Date) + 1 And Range("'Monthly Office Inventory'!A2") <> Date - Day(Date) + 1 Then

I'm thinking something like a nested IF check for a value between the first of last month and the end of last month, using the date functions.

Help please?

Upvotes: 1

Views: 737

Answers (1)

Heroz
Heroz

Reputation: 21

Sub Datetest()
Dim i As Integer
Dim x As Integer
Dim rng1 As Range
Dim rng2 As Range
Dim crninvbk As Worksheet
Dim mntlisbk As Worksheet
Set crninvbk = Worksheets("Current Office Inventory")
Set mntlisbk = Worksheets("sheet1")
PreviousMonth = DateAdd("m", -1, Date)
Negativemonthly = 0

For x = 2 To 23
    For i = 3 To 100
        If crninvbk.Cells(i, 1).value >= dhFirstDayInMonth And crninvbk.Cells(i, 1).value <= dhLastDayInMonth Then
            If crninvbk.Cells(i, x).value < 0 Then
            Negativemonthly = Negativemonthly + crninvbk.Cells(i, x).value
            End If
        End If
    Next i
    mntlisbk.Cells(2, x) = Negativemonthly
    MsgBox (Negativemonthly)
    Negativemonthly = 0
Next x
MsgBox (Negativemonthly)

End Sub
Function dhFirstDayInMonth(Optional dtmDate As Date = 0) As Date
    ' Return the first day in the specified month.
    PreviousMonth = DateAdd("m", -1, Date)
    dhFirstDayInMonth = DateSerial(Year(Date), _
     Month(PreviousMonth), 1)
End Function
Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
    ' Return the last day in the specified month.
    PreviousMonth = DateAdd("m", -1, Date)
    dhLastDayInMonth = DateSerial(Year(Date), _
     Month(PreviousMonth) + 1, 0)
End Function

Upvotes: 1

Related Questions