Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning single/multiple values from array function
Thanks in advance for any help provided for this problem
which has been bugging me for quite some time. I have various functions that return arrays of varying sizes. My problem is that, if my function returns only one value, Excel replicates that value over the entire resulting range. I'll try and explain further with a simple example. I've created a function that looks at a range of numbers and returns an array containing only the positive numbers. I've included the source code below for reference. If the source range contains at least two positive numbers, it works fine (the two positive numbers are returned and the remaining cells in the target range return #N/A, which I can deal with). However, if there is only a single positive number in the source range, that number is returned in each and every cell in the target range. In other words, if my source range contains the following: 1 -1 2 -3 I get: 1 2 #N/A #N/A But if my source range contains the following: 1 -1 -2 -3 I get: 1 1 1 1 The source code for the sample function is as follows: Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Double ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow ReDim Preserve Result(ResultRow - 1 Test = Application.Transpose(Result) End Function Apologies for the long-winded explanation, but I wanted to be as clear as possible. Thanks, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning single/multiple values from array function
Ryan,
Don't know if this will work for you. All numbers that are not positive return as zero. Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result(1 To 100) ResultRow = 1 For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow Test = Application.Transpose(Result) End Function Don Pistulka "Ryan Poth" wrote in message ... Thanks in advance for any help provided for this problem which has been bugging me for quite some time. I have various functions that return arrays of varying sizes. My problem is that, if my function returns only one value, Excel replicates that value over the entire resulting range. I'll try and explain further with a simple example. I've created a function that looks at a range of numbers and returns an array containing only the positive numbers. I've included the source code below for reference. If the source range contains at least two positive numbers, it works fine (the two positive numbers are returned and the remaining cells in the target range return #N/A, which I can deal with). However, if there is only a single positive number in the source range, that number is returned in each and every cell in the target range. In other words, if my source range contains the following: 1 -1 2 -3 I get: 1 2 #N/A #N/A But if my source range contains the following: 1 -1 -2 -3 I get: 1 1 1 1 The source code for the sample function is as follows: Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Double ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow ReDim Preserve Result(ResultRow - 1 Test = Application.Transpose(Result) End Function Apologies for the long-winded explanation, but I wanted to be as clear as possible. Thanks, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning single/multiple values from array function
Change your function to return Variant and adjust the output as shown
Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Variant ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow If ResultRow < 3 Then ReDim Preserve Result(1) Result(1) = CVErr(xlErrNA) Else ReDim Preserve Result(ResultRow - 1) End If Test = Application.Transpose(Result) End Function Regards, Tom Ogilvy Ryan Poth wrote in message ... Thanks in advance for any help provided for this problem which has been bugging me for quite some time. I have various functions that return arrays of varying sizes. My problem is that, if my function returns only one value, Excel replicates that value over the entire resulting range. I'll try and explain further with a simple example. I've created a function that looks at a range of numbers and returns an array containing only the positive numbers. I've included the source code below for reference. If the source range contains at least two positive numbers, it works fine (the two positive numbers are returned and the remaining cells in the target range return #N/A, which I can deal with). However, if there is only a single positive number in the source range, that number is returned in each and every cell in the target range. In other words, if my source range contains the following: 1 -1 2 -3 I get: 1 2 #N/A #N/A But if my source range contains the following: 1 -1 -2 -3 I get: 1 1 1 1 The source code for the sample function is as follows: Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Double ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow ReDim Preserve Result(ResultRow - 1 Test = Application.Transpose(Result) End Function Apologies for the long-winded explanation, but I wanted to be as clear as possible. Thanks, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning single/multiple values from array function
The 3 should be a 2
Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Variant ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow If ResultRow < 2 Then ReDim Preserve Result(1) Result(1) = CVErr(xlErrNA) Else ReDim Preserve Result(ResultRow - 1) End If Test = Application.Transpose(Result) End Function -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Change your function to return Variant and adjust the output as shown Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Variant ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow If ResultRow < 3 Then ReDim Preserve Result(1) Result(1) = CVErr(xlErrNA) Else ReDim Preserve Result(ResultRow - 1) End If Test = Application.Transpose(Result) End Function Regards, Tom Ogilvy Ryan Poth wrote in message ... Thanks in advance for any help provided for this problem which has been bugging me for quite some time. I have various functions that return arrays of varying sizes. My problem is that, if my function returns only one value, Excel replicates that value over the entire resulting range. I'll try and explain further with a simple example. I've created a function that looks at a range of numbers and returns an array containing only the positive numbers. I've included the source code below for reference. If the source range contains at least two positive numbers, it works fine (the two positive numbers are returned and the remaining cells in the target range return #N/A, which I can deal with). However, if there is only a single positive number in the source range, that number is returned in each and every cell in the target range. In other words, if my source range contains the following: 1 -1 2 -3 I get: 1 2 #N/A #N/A But if my source range contains the following: 1 -1 -2 -3 I get: 1 1 1 1 The source code for the sample function is as follows: Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Double ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow ReDim Preserve Result(ResultRow - 1 Test = Application.Transpose(Result) End Function Apologies for the long-winded explanation, but I wanted to be as clear as possible. Thanks, Ryan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning single/multiple values from array function
Tom,
What a good idea. I'm ashamed I hadn't thought of it, but then that's why I love these newsgroups. Thanks very much again. Ryan -----Original Message----- The 3 should be a 2 Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Variant ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow If ResultRow < 2 Then ReDim Preserve Result(1) Result(1) = CVErr(xlErrNA) Else ReDim Preserve Result(ResultRow - 1) End If Test = Application.Transpose(Result) End Function -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Change your function to return Variant and adjust the output as shown Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Variant ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow If ResultRow < 3 Then ReDim Preserve Result(1) Result(1) = CVErr(xlErrNA) Else ReDim Preserve Result(ResultRow - 1) End If Test = Application.Transpose(Result) End Function Regards, Tom Ogilvy Ryan Poth wrote in message ... Thanks in advance for any help provided for this problem which has been bugging me for quite some time. I have various functions that return arrays of varying sizes. My problem is that, if my function returns only one value, Excel replicates that value over the entire resulting range. I'll try and explain further with a simple example. I've created a function that looks at a range of numbers and returns an array containing only the positive numbers. I've included the source code below for reference. If the source range contains at least two positive numbers, it works fine (the two positive numbers are returned and the remaining cells in the target range return #N/A, which I can deal with). However, if there is only a single positive number in the source range, that number is returned in each and every cell in the target range. In other words, if my source range contains the following: 1 -1 2 -3 I get: 1 2 #N/A #N/A But if my source range contains the following: 1 -1 -2 -3 I get: 1 1 1 1 The source code for the sample function is as follows: Function Test(Source As Range) As Variant Dim SourceRow As Integer, ResultRow As Integer Dim Result() As Double ReDim Result(100) For SourceRow = 1 To Source.Rows.Count If Source.Cells(SourceRow, 1).Value 0 Then Result(ResultRow) = Source.Cells(SourceRow, 1).Value ResultRow = ResultRow + 1 End If Next SourceRow ReDim Preserve Result(ResultRow - 1 Test = Application.Transpose(Result) End Function Apologies for the long-winded explanation, but I wanted to be as clear as possible. Thanks, Ryan . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning multiple values that are NOT in a single column or row | Excel Worksheet Functions | |||
VLOOKUP Function returning multiple values in a separate table | Excel Worksheet Functions | |||
Returning an array of unique values? | Excel Worksheet Functions | |||
returning multiple cells for a single lookup | Excel Discussion (Misc queries) | |||
Matching numbers in an Array and returning values for matched numb | Excel Discussion (Misc queries) |