Microsoft KB Archive/37498

Using SET.VALUE Formula with Array Name in Excel PSS ID Number: Q37498 Article last modified on 02-26-1993 PSS database name: W_eXceL

2.00 2.01 2.10 3.00 | 2.20 2.21 3.00 | 2.x 3.00

WINDOWS | OS/2 | MACINTOSH

Summary:

When the name Data on a macro sheet is used to refer to the array {1,2,3,4}, the value 1 is incorrectly transferred into cells B1:E1 when the the following SET.VALUE command is used:

=SET.VALUE(B1:E1,Data)

The values are transferred correctly if the following formula is entered:

=SET.VALUE(B1:E1,{1,2,3,4})

To use a name that references an array, the formula (in this case, the SET.VALUE command) must be entered as an array (press CTRL+SHIFT+ENTER).

More Information:

In the first example above, the values 1,2,3,4 will be entered in B1:E1 once the SET.VALUE formula is entered as an array.

Additional reference words: 2.00 2.0 2.01 2.1 2.10 2.2 2.21 2.20 3.00 3.0

Copyright Microsoft Corporation 1993.