Re: HELP replacing cell references in equations with $X$Y format
- From: "SixSigmaGuy" <Herrick.Andrews@xxxxxxx>
- Date: Tue, 29 Jul 2008 16:46:05 -0700
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. :-)
<Johndagolfer@xxxxxxxxx> wrote in message
news:ed5cdab4-8835-49b0-8209-bb8e480cc9ce@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- Prev by Date: vacation formula
- Next by Date: RE: Help with a nested IF statement
- Previous by thread: vacation formula
- Next by thread: Re: HELP replacing cell references in equations with $X$Y format
- Index(es):
Relevant Pages
|
