Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 17
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 18
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning multiple values that are NOT in a single column or row crispino Excel Worksheet Functions 8 April 24th 09 02:45 AM
VLOOKUP Function returning multiple values in a separate table CAT Excel Worksheet Functions 8 October 1st 08 02:53 PM
Returning an array of unique values? Blue Max Excel Worksheet Functions 10 January 16th 08 03:51 AM
returning multiple cells for a single lookup esloan Excel Discussion (Misc queries) 2 August 22nd 07 06:52 PM
Matching numbers in an Array and returning values for matched numb Tiger Excel Discussion (Misc queries) 8 April 26th 07 06:14 AM


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"