Result posts before Function executes
 

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

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

Result posts before Function executes
 

Thanx...That did it!

"Tom Ogilvy" wrote:

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