PDA

View Full Version : How to get a cell property?



rdekanter
10-10-2018, 05:21 AM
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
10-10-2018, 05:37 AM
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
10-10-2018, 08:18 AM
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