Help with unwanted R1C1 References
 

In Excel i have done a group change with a simple formula (a1*b1 in cell c1)
and it comes up with the reference +RC[-1]*RC[-2] in all active sheets apart
from the one i am using. I have tried to stop it, the help file only advises
me to turn off R1C1 in options - it already is! Please help as i have to do
this many times to over 40 worksheets!

Thanks

Help with unwanted R1C1 References
 

It's interesting to see the inners of excel, huh?

When you enter a formula, excel "sees/parses" it as R1C1 reference style--but it
displays the formula the way you want according to that setting.

Your problem is that in the other sheets, C1 is formatted as Text. Change the
format to General (or Number or whatever you want except Text) and you'll be ok.

Ps. In excel, most people start the formula with an equal sign (not + like in
Lotus 123).



Tony Black wrote:

In Excel i have done a group change with a simple formula (a1*b1 in cell c1)
and it comes up with the reference +RC[-1]*RC[-2] in all active sheets apart
from the one i am using. I have tried to stop it, the help file only advises
me to turn off R1C1 in options - it already is! Please help as i have to do
this many times to over 40 worksheets!

Thanks

--

Dave Peterson

Help with unwanted R1C1 References
 

Hi Tony

I don't if this can help
I am not sure, but if you want to change the string "R3C4" to the
string
"D3", then here is one way to do it.


Sub Example()
'// Convert string R3C4 to string D3
Dim Row, Col, NewAddress, sStr
sStr = "R3C4"
With Application
Row = Val(Mid(sStr, 2))
Col = Val(Mid(sStr, .Search("C", sStr) + 1))
End With
NewAddress = Cells(Row, Col).Address _
(RowAbsolute:=False, ColumnAbsolute:=False,
ReferenceStyle:=xlA1)
End Sub


--
jetted
------------------------------------------------------------------------
jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532
View this thread: http://www.excelforum.com/showthread...hreadid=564339