Result posts before Function executes

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanx...That did it!

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