Unknown error in function, and how to return value?

[Chris]

Hi all,

I've been working on this sheet for a bit, and I'm stuck at this point,
where I have a worksheet created like a calendar. My goal is to have a
function that pulls the date from the cell above it, searches another
worksheet in column "M" for matches to that date, add all of those rows'
column "O" together, and return the sum to range where I called the function.
Below is what I came up with, but I'm getting an error and don't know why,
and I also don't know how to return the variable.

Function SalesTotal()
Dim varDate As Date
Dim LSearchRow, varSaleTotal As Integer
' Search date needs to be same column, one row up
varDate = Range((Target.Row - 1) & Target.Column).Value


On Error GoTo Err_Execute
' Setup search from sheet 17

Sheet17.Select
LSearchRow = 2
While Len(Range("A" & CInt(LSearchRow)).Value) > 0
If Range("M" & CInt(LSearchRow)).Value = varDate Then


'Add found range to var

varSaleTotal = varSaleTotal + (Range("O" &
CInt(LSearchRow)).Value)

'Go back to Sheet17 to continue searching

Sheet17.Select
End If
LSearchRow = LSearchRow + 1
Wend
Exit Function

Err_Execute:
MsgBox "An error occurred."


End Function

Thanks in advance for any help.

 

[Mike H]

Chris,

A slightly different aproach.

Function SalesTotal() As Long
Dim varDate As Variant
Dim c As Range
varDate = ActiveCell.Offset(-1).Value
LastRow = Sheets("Sheet17").Cells(Rows.Count, "M").End(xlUp).Row
Set MyRange = Sheets("Sheet17").Range("M" & ActiveCell.Row & ":M" & LastRow)
For Each c In MyRange
If c.Value = varDate Then
SalesTotal = SalesTotal + c.Offset(, 2).Value
End If
Next
If IsError(SalesTotal) Then SalesTotal = 0
End Function

Mike

 

[Chris]

Ok, I can tell it's close, because some dates are working.... But some
aren't, and I can't tell why. I tried to disable the "if error" part by
making it a comment and it was still showing "0" under some dates that should
not have... That tells me it obviously didn't error out, but for some reason
it isn't including the rows for those dates.

Here's the function exactly:

Function SalesTotal() As Integer
Dim varDate As Date
Dim c As Range
varDate = ActiveCell.Offset(-1).Value
LastRow = Sheet19.Cells(Rows.Count, "M").End(xlUp).Row
Set MyRange = Sheet19.Range("M" & ActiveCell.Row & ":M" & LastRow)
For Each c In MyRange
If c.Value = varDate Then
SalesTotal = SalesTotal + c.Offset(, 2).Value
End If
Next
If IsError(SalesTotal) Then SalesTotal = 0
End Function

If you notice, the sheet is now by number. Also, I found that the only date
this is actually working on is 5/15/09. They're all in there exactly the
same with the dates showing but that's the only one that is correct, and not
0.

Thanks again

 

[Chris]

Mike, you're my hero. Here's the final script/function with a couple changes:

Function SalesTotal() As Integer
Dim varDate As Date
Dim c As Range
varDate = ActiveCell.Offset(-1).Value
SalesTotal = 0
LastRow = Sheet19.Cells(Rows.Count, "M").End(xlUp).Row
Set MyRange = Sheet19.Range("M1:M" & LastRow)
For Each c In MyRange
If c.Value = varDate Then
SalesTotal = (SalesTotal + c.Offset(, 2).Value)
End If
Next
If IsError(SalesTotal) Then SalesTotal = 0
End Function