How to get a cell property?

[rdekanter]

Hello,

I am looking to use a short script to cycle the relative / absolute nature of a cell. To do this I know I can use

cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, 1)

where 1 is the equivalent of xlAbsolute

What I would like to do is have this cycle, so if it is already absolute, it will use 2 instead which is the equivalent of xlAbsRowRelColumn. It would then go to 3, 4 and the back to 1 etc.

To do this I would like to simply read the current state of xlReferenceType for a given cell and then increment it with a mod function, however I do not know how to read the state of xlReferenceType (or any Enumerations for that matter).

Any ideas how to do this please?

Thanks

[rdekanter]

This solution occurred to me:

For Each cell In Selection    'Determine current reference type
    Select Case cell.Formula
        'xlAbsolute
        Case Application.ConvertFormula(cell.Formula, xlA1, xlA1, 1): ref = 2
        'xlAbsRowRelColumn
        Case Application.ConvertFormula(cell.Formula, xlA1, xlA1, 2): ref = 3
        'xlRelRowAbsColumn
        Case Application.ConvertFormula(cell.Formula, xlA1, xlA1, 3): ref = 4
        'xlRelative
        Case Application.ConvertFormula(cell.Formula, xlA1, xlA1, 4): ref = 1
    End Select
    
    'Cycle reference type
    cell.Formula = Application.ConvertFormula(cell.Formula, xlA1, xlA1, ref)
Next

[snb]

Or:

Sub M_snb()
  For Each it In Cells.SpecialCells(-4123)
    For j = 1 To 4
      If it.Formula = Application.ConvertFormula(it.Formula, 1, 1, j) Then Exit For
    Next
    it.Formula = Application.ConvertFormula(it.Formula, 1, 1, 1 + j Mod 4)
  Next
End Sub