Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to read own posts or replied Posts | Excel Discussion (Misc queries) | |||
For Loop only executes with F8 | Excel Programming | |||
SAveAs executes combobox code | Excel Programming | |||
excel executes only a part of a macro | Excel Programming | |||
Count # of Times Sub Executes | Excel Programming |