Function for summing values of a field by date criteria
 

Hi All

Just started using VB(A). I just finished writing this function that
works fine but was interested in getting some feedback on how I could
improve it or other methods of approaching the problem.

Requirement:
To be able to sum values of a table/list (in Excel) within a given
period.

e.g. for Table 1:

Somefunction(2004/01, 2004/03) = 18


Example of tables:

Table 1:

Column A Column B

Date CountOfRecords
======= ==============
2004/01 10
2004/02 5
2004/03 3
2004/06 9


Table 2:

Date CountOfRecords
======= ==============
2003/01 1
2003/03 5
3
2003/06 11




VBA code:

================================================== =================
Function HistoricTotal(text1 As String, text2 As String) As Integer

Dim rng As Range
Dim Counter As Integer

Counter = 0

Set rng = Sheets("Sheet1").Range("A:B")

If text1 = text2 Then
HistoricTotal = Application.VLookup(text1, rng, 2, False)
Else
While text1 <= text2
On Error Resume Next
Counter = Counter + Application.VLookup(text1, rng, 2, False)

text1 = Application.Text((DateAdd("m", 1, text1)), "yyyy/mm")

Wend

HistoricTotal = Counter
End If

End Function
================================================== ===================


Thanks

Lawrence

Function for summing values of a field by date criteria
 

If your going to use worksheet functions then

=sumif(A:A,"=2004/01",B:B)-sumif(A:A,"2004/03",B:B)

would work in a cell, so you can use the same thing in your function.

Function HistoricTotal(text1 As String, text2 As String) As Long
Dim Tot as Long
with Application
tot = .sumif(Range("A:A"),"=" & Text1, Range("B:B"))- _
.sumif(Range("A:A"),"" & text2, Range("B:B"))
End With
HistoricTotal = Tot
End Function

I haven't tested it with your unusual date format, but assume it won't be
misinterpreted as division, or if it is, that it will still work.


--
Regards,
Tom Ogilvy


"Lawrence" wrote in message
om...
Hi All

Just started using VB(A). I just finished writing this function that
works fine but was interested in getting some feedback on how I could
improve it or other methods of approaching the problem.

Requirement:
To be able to sum values of a table/list (in Excel) within a given
period.

e.g. for Table 1:

Somefunction(2004/01, 2004/03) = 18


Example of tables:

Table 1:

Column A Column B

Date CountOfRecords
======= ==============
2004/01 10
2004/02 5
2004/03 3
2004/06 9


Table 2:

Date CountOfRecords
======= ==============
2003/01 1
2003/03 5
3
2003/06 11




VBA code:

================================================== =================
Function HistoricTotal(text1 As String, text2 As String) As Integer

Dim rng As Range
Dim Counter As Integer

Counter = 0

Set rng = Sheets("Sheet1").Range("A:B")

If text1 = text2 Then
HistoricTotal = Application.VLookup(text1, rng, 2, False)
Else
While text1 <= text2
On Error Resume Next
Counter = Counter + Application.VLookup(text1, rng, 2, False)

text1 = Application.Text((DateAdd("m", 1, text1)), "yyyy/mm")

Wend

HistoricTotal = Counter
End If

End Function
================================================== ===================


Thanks

Lawrence