How to Use Multiple Objects 1

[jessedh]

Hi Everyone!

I think that my problem is relatively simple, I just can't visualize how to solve it. I need to create and excel function (SumMonth) which I pass 3 variable into.

1)Range of Dates in a column
2)Corresponding Range of $ Values in a column
3)Single Cell Range of Month to Sum for.

I have parts 1 and 3 done and can get it to count the number of occurances of a date in a range, I just can't figure out how to keep a running sum of the corresponding $ values.

Any help is appreciated!

Here is the Code I have so far...
_____________________________________________
Function GetMonth(test As String) As Integer

Dim FindMonth


If (test Like "*JANUARY*&quot = "True" Then
GetMonth = 1
Else
GetMonth = 2
End If

End Function
______________________________________________
Function SumMonth(DateRange As Range, MonthRange As Range) As Integer

On Error GoTo errhandler

'Range1 as Month
'Range2 as NoteAmount


Dim intCellMonth As String
Dim intCountMonth As Integer
Dim Cell As Object
Dim CellMasterMonth As Object
Dim CellMonth As String
Dim test As String


'Get String of Month

For Each CellMasterMonth In MonthRange

test = CellMasterMonth.Value

intCellMonth = GetMonth(test)



For Each DateCell In DateRange

CellMonth = Format(DateCell.Value, "m&quot

Select Case CellMonth
Case intCellMonth
intCountMonth = intCountMonth + 1
End Select

Next DateCell

Next CellMasterMonth

SumMonth = intCountMonth

Exit Function

'-----------------------------------------------------
'Error Handler

errhandler:
MsgBox "You have encountered " & Err.Number & ", " & Err.Description
Exit Function


End Function

 

[Zathras]

Here is a simplified function which does what you describe. You will need to add your own error handling code and it is expecting a month number instead of a name as the third parameter (which you could also reprogram):

Function SumMonth(Dates As Range, _
   DValues As Range, WhichMonth As Integer) As Variant
Dim d As Range
Dim nRow As Long

nRow = 0
For Each d In Dates
  nRow = nRow + 1
  If Month(d.Value) = WhichMonth Then
    SumMonth = SumMonth + DValues.Cells(nRow, 1)
  End If
Next d

End Function

 

[jessedh]

Thanks, that was pretty much perfect, I didn't even know the Month() function existed!

If you get a chance, could you try to explain how the range object works, or point me to a good explanation on the web...

Thanks!
Jesse

 

[Zathras]

The help file is the place to start. (Note: VBA help, NOT Excel help.) It may take a while to find the right page, however. Just keep looking until you find "Range Object" then explore each property and method one at a time.