Help with unwanted R1C1 References

[Guest]

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]

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).

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

 

[jetted]

Hi Tony

I don't if this can help
I am not sure, but if you want to change the string "R3C4" to th
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 Su