P
PeteCresswell
After I get done building a sheet in VBA (which
has .ReferenceStyle=xlR1C1), I have a cell where .FormulaR1C1=R6C15/
R111C15.
Users, however, do not care for that reference style, so at the end of
my coding I do a .ReferenceStyle=xlA1.
At that time, abovementioned cell's .FormulaR1C1 changes to "=$O$6/$O
$111". I.E. an absolute reference.
Same thing, of course, happens if I rem out the ref style change and
just do an Options | General | R1C1 reference style = False.
In retrospect, this seems like that's the way it has to be: that
"R6C15/R111C15" is inherantly an absolute reference.
A problem arises, however: the user sometimes re-sorts the sheet.
The row, of course dutifully moves to it's expected location depending
on the sort criteria. But the absolute reference remains and the
number for that cell is now incorrect - being computed from some other
row's data.
In the example, I want the "R5C15" part of the formula to be relative
and the "R11C15" part of the formula to be absolute.
Can somebody put me on the Good-Right-And-Holy path VBA-wise?
Maybe keeping .ReferenceStyle=xlA1, but invoking some kind of column
number conversion function to get the alphabetic equivalent of a
column number?
has .ReferenceStyle=xlR1C1), I have a cell where .FormulaR1C1=R6C15/
R111C15.
Users, however, do not care for that reference style, so at the end of
my coding I do a .ReferenceStyle=xlA1.
At that time, abovementioned cell's .FormulaR1C1 changes to "=$O$6/$O
$111". I.E. an absolute reference.
Same thing, of course, happens if I rem out the ref style change and
just do an Options | General | R1C1 reference style = False.
In retrospect, this seems like that's the way it has to be: that
"R6C15/R111C15" is inherantly an absolute reference.
A problem arises, however: the user sometimes re-sorts the sheet.
The row, of course dutifully moves to it's expected location depending
on the sort criteria. But the absolute reference remains and the
number for that cell is now incorrect - being computed from some other
row's data.
In the example, I want the "R5C15" part of the formula to be relative
and the "R11C15" part of the formula to be absolute.
Can somebody put me on the Good-Right-And-Holy path VBA-wise?
Maybe keeping .ReferenceStyle=xlA1, but invoking some kind of column
number conversion function to get the alphabetic equivalent of a
column number?