HELP replacing cell references in equations with $X$Y format
 

Hi all,

Let me explain what I am trying to do.

I am trying to create a form where data can be input horizontally on
one worksheet of a document and then transposed automatically to
columns on a seperate worksheet within the same document.

I am on a short time line so I have to be able to do this quickly.

What I tried to do was copy over the reference to each cell but got a
REF error. After reading a few posts I found that the references
needed to be in a $x$y format for this to work. I have unfortunately
255 x 30 cells to change and would take quite some time to do this.

I am not very good at macros yet so I was wondering how would I be
able to make this change quickly?

Thanks,

John

HELP replacing cell references in equations with $X$Y format
 

First of all, if you are using Excel 2007, it has a new transpose function
built in. Just select the data you want to transpose, copy it to the
clipboard, select where you want the transposed data to go, and then select
"Transpose" from the paste menu. Now, if you also start "record macro" and
repeat these steps, Excel will produce the code it used to transpose the
data. Here's what I got when I did that:

Range("A26:F26").Select
Selection.Copy
Range("A27").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False

Secondly, I never heard anything suggesting that the references had to be in
the form of $x$y. Also, I'm assuming you mean $A$1 format as I've never
heard of something called $x$y format. Please tell me if I'm wrong here.
The two formats I'm aware of are "A1" and "R1C1" where A1 is like the
address "A26:F26" and R1C1 would be "R26C1:R26C6" although I don't know if
you can use the R1C1 format to specify an address, I've only seen it used as
a way to return an address. To specify an address in a row and column
format, I use the Cells(R,C) construct, so,

Range("A26:F26") is the same as Range(Cells(26,1), Cells(26,6)).

if you want references in $A$1 format, just query the Address property of
any range. E.g.,

"Range(Cells(26,1), Cells(26,6)).Address" is equal to "$A$26:$F$26"

Something tells me I've completely missed the crux of your question. :-)
But hopefully my response is helpful. :-)


wrote in message
...
Hi all,

Let me explain what I am trying to do.

I am trying to create a form where data can be input horizontally on
one worksheet of a document and then transposed automatically to
columns on a seperate worksheet within the same document.

I am on a short time line so I have to be able to do this quickly.

What I tried to do was copy over the reference to each cell but got a
REF error. After reading a few posts I found that the references
needed to be in a $x$y format for this to work. I have unfortunately
255 x 30 cells to change and would take quite some time to do this.

I am not very good at macros yet so I was wondering how would I be
able to make this change quickly?

Thanks,

John

HELP replacing cell references in equations with $X$Y format
 

Sub LinkTransposed()

Dim rngSource As Range, rngDest As Range, x, y

Set rngSource = ActiveWorkbook.Sheets("Sheet1").Range("B3:I21")
Set rngDest = ActiveWorkbook.Sheets("Sheet2").Range("B3") 'top left cell

For x = 1 To rngSource.Columns.Count
For y = 1 To rngSource.Rows.Count
rngDest.Offset(x - 1, y - 1).Formula = _
"=" & rngSource.Cells(y, x).Address(False, False, , True)
Next y
Next x

End Sub


Tim


wrote in message
...
Hi all,

Let me explain what I am trying to do.

I am trying to create a form where data can be input horizontally on
one worksheet of a document and then transposed automatically to
columns on a seperate worksheet within the same document.

I am on a short time line so I have to be able to do this quickly.

What I tried to do was copy over the reference to each cell but got a
REF error. After reading a few posts I found that the references
needed to be in a $x$y format for this to work. I have unfortunately
255 x 30 cells to change and would take quite some time to do this.

I am not very good at macros yet so I was wondering how would I be
able to make this change quickly?

Thanks,

John