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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
See if this is something you can work with.
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
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.
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.
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.