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



"Mike H" wrote:

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" wrote:

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.