array variables

I don't know how it's possible, but I have a reversed defining variables. Horizontal is Vertical and Vertical is Horizontal. How to fix it? Please see example below. If I use";" to separate values then it should works for rows, not for columns - right?
1.png
2.png

Select cells B1:F1 and type

Formula:

Please Login or Register  to view this content.

and press Ctrl-Shift Enter to commit the formula rather than just Enter

Select cells A2:A6 and type

Formula:

Please Login or Register  to view this content.

and press Ctrl-Shift Enter to commit the formula rather than just Enter

Regards, TMS

You are correct that ; is for rows and , is for columns.

Here is something to play with.
1. Select A1:A5 and enter the following formula with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

The result should be all 1s down the column.

2. Select A1:A5 click on Find and Select, Replace.
3. In Find What enter ,
4. In Replace With enter ;
5. Replace All

The result should be the numbers from 1 to 5 down the column

Try the same thing in row 1. Select A1 to E1 and enter

Formula:

Please Login or Register  to view this content.

When you enter with Ctrl + Shift + Enter the result should be all 1s
Now select A1:E1 click on Find and Select, Replace
In the Find What enter ;
In the Replace With enter ,
Replace All

The result should be the numbers from 1 to 5 in the row.

I hope that this helps.

Originally Posted by newdoverman

You are correct that ; is for rows and , is for columns.

Here is something to play with.
1. Select A1:A5 and enter the following formula with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

The result should be all 1s down the column.

2. Select A1:A5 click on Find and Select, Replace.
3. In Find What enter ,
4. In Replace With enter ;
5. Replace All

The result should be the numbers from 1 to 5 down the column

Try the same thing in row 1. Select A1 to E1 and enter

Formula:

Please Login or Register  to view this content.

When you enter with Ctrl + Shift + Enter the result should be all 1s
Now select A1:E1 click on Find and Select, Replace
In the Find What enter ;
In the Replace With enter ,
Replace All

The result should be the numbers from 1 to 5 in the row.

I hope that this helps.

If I use "," then get an error. You can only use ";" for rows and "\" for columns in array. Not "," to separate values

Please note that if I work in another computer then everthing is ok.

I suspect that you have changed your Regional Settings.

Regards, TMS

How about Options > Advanced > Editing Options, Use System Separators?

Here is an interesting bit on the separators for French regional settings.
http://stackoverflow.com/questions/2...-excel-formula

Everything that I have found indicates as TMS suggests that the Regional Settings are at play here.

If you have the Regional settings correct you should get the correct results. The problem lies when a conversion has to be manually made from one regions settings to another.

What region has settings that are causing you problems?... just curious.