Result posts before Function executes
A function used in a worksheet can not change the selection. Also, I don't
think special cells works properly when the function is used in a worksheet.
Here is a fix:
Function fn_SummaryPage(in_date As Date) As Double
Dim nd_row As Long, is_Rt As Long
Dim rng as Range
fn_SummaryPage = 0
With Sheets("DATA")
set rng = .Cells(rows.count,"F").End(xlup)
nd_row = rng.Row
For i = 1 To nd_row
If .Cells(i, "F").Value = in_date Then
is_Rt = fn_FindRT(.Cells(i, "S"))
fn_SummaryPage = fn_SummaryPage + (is_Rt * .Cells(i, "K"))
End If
Next i
End With
End Function
--
Regards,
Tom Ogilvy
"BAC" wrote:
XP Pro, Office 2003 Pro
I"ve never run into this before. I've got a user defined function:
Function fn_SummaryPage(in_date As Date) As Double
Dim nd_row As Integer, is_Rt As Integer
fn_SummaryPage = 0
With Sheets("DATA")
.Select
.Cells(1, 1).Select
ActiveCell.SpecialCells(xlLastCell).Select
nd_row = Selection.Row
For i = 1 To nd_row
If .Cells(i, "F").Value = in_date Then
is_Rt = fn_FindRT(.Cells(i, "S"))
fn_SummaryPage = fn_SummaryPage + (is_Rt * .Cells(i, "K"))
End If
Next i
End With
End Function
The function is entered in an equation on sheets("Summary") (as: =
fn_SummaryPage(A4) where A4 is a relevant date. When I calculate, the value
in the cell on sheets("Summary") goes to #VALUE.
I've stepped thru the function and found this occurs as soon as the
.Cells(1, 1).Select
line executes (So as soon as the function selects a cell "off sheet", the
function terminates and returns "#VALUE" in the cell on sheets("Summary")
where the formula is.
I cannot use sumproduct or Sum + If because the data on sheets("DATA") is
mixed format and has multiple sections with various length sections and blank
rows between them.
TIA
BAC
|