Unknown error in function, and how to return value?
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. |
Unknown error in function, and how to return value?
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. |
Unknown error in function, and how to return value?
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. |
Unknown error in function, and how to return value?
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. |
All times are GMT +1. The time now is 04:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com