Consulting

Results 1 to 3 of 3

Thread: How to get a cell property?

  1. #1

    How to get a cell property?

    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

  2. #2
    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

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,416
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •