Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular References - Simultaneous Equations | Excel Discussion (Misc queries) | |||
Cell References Format | Excel Discussion (Misc queries) | |||
Replacing Sheet references within formulas with VB | Excel Programming | |||
Replacing references from one file to another | Excel Programming | |||
Replacing Named Range Names By Cell References in Formulas | Excel Programming |