Link to home
Start Free TrialLog in
Avatar of johngee123
johngee123

asked on

Excel Transpose Function

There is a Transpose function in Excel that lets me move a string of data from a column to a row and from a row to a column..the function looks like this:( =TRANSPOSE(A3:CW3). I think that it's called an Array

Excel won't let me change anything in an array and if I try (by mistake) to change anything I get a message and the only way out is Cntrl Alt Delete to exit Excel and start all over again.

The programs that I use that make use of arrays are at least five years old and my question is has Microsoft or anyone else found a way to change Arrays

Thanks

John
Avatar of Professor J
Professor J

Yes, when Transpose is used, you cannot change part of the array.

to change that you need to refer to the original data from where the Transpose is driving out the output.  make a change in there and the reflection will be in the Transposed data.
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just to add, editing any formula in Excel does not, and never has, require a restart. If it does for you, there's something else at work.
Rather than using the Transpose function, you can use a code to transpose the values if your program allows that.
If so, Right Click the Sheet Tab --> View Code --> And paste the code given below in the opened code window --> Close the VBA Editor --> Save your workbook as Excel Macro-Enabled Workbook.

The following code uses the BeforeDoubleClick Event.
So after the code is placed on the sheet module as described above, you may double click the cell where you want to place the transpose values, the code will prompt you to select the First cell in the range (in this case, it would be A3), then the code will prompt you again to select the Last cell in the range (in this case it would be CW3) and as a result the code will transpose the values of the selected range and paste them below the cell which was double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sCell As Range, eCell As Range
Dim n As Long
Dim arr As Variant
Set sCell = Application.InputBox("Select the first cell in the range.", Type:=8)
Set eCell = Application.InputBox("Select the last cell in the range", Type:=8)
n = Range(sCell, eCell).Cells.Count
arr = Range(sCell, eCell).Value
Target.Resize(n, 1).Value = Application.Transpose(arr)
Cancel = True
End Sub

Open in new window


See if this is something you can work with.
I've requested that this question be closed as follows:

Accepted answer: 168 points for ProfessorJimJam's comment #a40861451
Assisted answer: 166 points for Rory Archibald's comment #a40861589
Assisted answer: 166 points for sktneer's comment #a40862148

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.