.ReferenceStyle=xlA1 Converting Relative to Absolute References?

[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?

 

[JLGWhiz]

The best I can do is tell you that when you enter a formula in a worksheet,
you need to be aware of what will happen to the cell references if the data
range is sorted or rows and columns are subject to addition and deletion.
Understanding how the relative and absolute cell references work is key to
maintaining formula integrity. Look up "About cell and range references" in
Excel help and "How to Reference Cells and Ranges" in the VBE help. These
provide detailed explanations of the use of cell reference styles.

 

[Charles Williams]

You can do relative references in R1C1 mode: R[-2]C[4] refers to the cell 2
rows above and the column 2 cells to the right.

Or you can convert numbers to column strings if thats what you want to do,
here is the function I use:

Public Function strColid(jColNo As Long) As Variant
If jColNo > 0 And jColNo <= 16384 Then
If jColNo < 27 Then
strColid = Chr$(((jColNo - 1) Mod 26) + 65)
ElseIf jColNo < 703 Then
strColid = Chr$(64 + Int((jColNo - 1) / 26)) & Chr$(((jColNo - 1) Mod
26) + 65)
Else
strColid = Chr$(64 + Int((jColNo - 27) / 676)) & Chr$(64 + _
Int((jColNo - Int((jColNo - 27) / 676) * 676 - 1) / 26)) & Chr$(((jColNo -
1) Mod 26) + 65)

End If
Else
strColid = CVErr(xlErrNA)
End If
End Function

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[PeteCresswell]

Or you can convert numbers to column strings if thats what you want to do,
here is the function I use:

That did the trick quite nicely.

Thanks.