Array UDF keeps repeating single value

Question

I use an array UDF to select elements from a large data file that comply with several criteria. The formula works fine when I have multiple eligible elements, but when there is only one eligible element. The element keeps getting repeated indefinitely.

Example output multiple eligible elements:

A  
B  
C  
N/A  
N/A

Example output 1 eligible elements:

A  
A  
A  
A  
A

The output array I pass in my UDF contains only 1 element so this seems to be an issue related array functions in general. For instance I can recreate the behaviour using the TRANSPOSE function on 1 cell.

Transpose example

Does somebody know a way to avoid this behaviour?

It's hard to know without seeing the code - [kindly post the code](http://stackoverflow.com/help/how-to-ask). — BruceWayne, Nov 08 '16 at 19:20
See http://stackoverflow.com/a/40451062/6535336 - if you copy a number of values to a range that is larger, but an exact multiple larger, the values get repeated - so copying two values to 8 cells will cause the two values to be repeated 4 times, copying one value to an entire column will cause the one value to be repeated 1048576 times, but copying 7 values to 24 cells will cause the values to only be inserted once (because 7 is not a factor of 24). So, how big is your target range? — YowE3K, Nov 08 '16 at 19:30

score 1 · Answer 1 · answered Nov 08 '16 at 19:51

If you're using a UDF in an Array Formula context then you can make sure your output array is the correct size by looking at

Application.Caller

inside the UDF.

This represents the range where you've entered your array formula. Use the dimensions of this range to resize your output array to avoid the problem you note in your question.

Array UDF keeps repeating single value

1 Answers1