Transform Single Row to Single Column with Referen

Question

Problem: I often need to refer a row to a column (using =Cell). When I need to do it with multiple row, I just use Ctrl+Shift+Enter. But it is annoying because error: "you cannot change part of an array" when I would like to edit something.

Explain expected result: I would like to choose the range which is the single row without a header.

Dim cRangeColumn As Range
Set cRangeColumn = Application.InputBox("Please choose a range", "Specify Range", Type:=8)

After I press "OK", it should let me choose where I put my row(single row which is transposed of the column) - a cell address to let the program knows where to start

Again, this can be done by

Dim rRangeRow As Range
Set rRangeRow = Application.InputBox("Please choose a range", "Specify Range", Type:=8)

Problem I face: How to access the value in range

The logic:

1. I get the range of the columns - let say $E$29:$E$37 - Total 9 value

2. I choose the start address of the transposed column (a row), let say Sheet226!$G$14

3. Program start to write ="$E$29 to the starting address "Sheet226!$G$14", then write until $E$37 is meet. Each time it writes, offset 1 column.

Answer 1

After swimming in the ocean, I was able to make this work

I try to use the "< code > " but it does not look well. So I take the snapshot of the code. It is not lengthy. I wish it does not bother.