Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Highlighting ARRAY formula cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Highlighting ARRAY formula cells

    Dear Forum,

    I thought I would be able to do this but its prooving a whole lot more difficult than I first thought. Please can you help?

    Essentially I have inherited a spreadsheet which is crammed full of complex array formulas.

    No problem in itself as I understand array formulas pretty well (or so I think!). My problem is that when i want to ammend some of these, i cannot easily see what range I need to highlight in order to edit them i.e. I obviously cant do a single cell as its part of an array. I hope I have made myself clear?

    So my question is this.

    When the cursor is sat in a cell that is part of an array formula, how can I easily find out ( ideally by the array formula range being highlighted in (say) red ) what cells are included in the array formula?

    Would conditional formating do the job?

    I would be grateful for any help given.

    Regards,

    Pete

  • #2
    Re: Highlighting ARRAY formula cells

    Aaron Blood has a free Formula Auditing Tool. I avoud array formulas as much as possible, so i don't know if it works with them.
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Highlighting ARRAY formula cells

      Roy,

      Thanks - an excellent utility in any event but unfortunately it doesn't solve my query highlighting array formulas (not that I could see anyway).

      Thanks again,

      pete

      Comment


      • #4
        Re: Highlighting ARRAY formula cells

        Hi All,

        I have been told two ways to do it - pretty simple really - either press "Ctrl /" or from the menu, "Edit | Goto | Special | current Array".

        Regards,

        Pete

        Comment


        • #5
          Re: Highlighting ARRAY formula cells

          F5 - Special - Current Array wont select all array formulas on a Worksheet unless they are all part of the same array the active cell is.
          Last edited by Dave Hawley; August 9th, 2006, 17:25.

          Comment


          • #6
            Re: Highlighting ARRAY formula cells

            Hmmm... wonder how I managed to miss this one?

            Code:
            Sub Highlight_Arrays()
                Dim cell As Range
                For Each cell In Selection.SpecialCells(xlFormulas)
                    If cell.HasArray Then cell.Interior.ColorIndex = 6 'yellow
                Next cell
            End Sub
            In which case it would be perfectly acceptable to select the entire sheet and analyze all formulas.

            Ah well, better late than never...
            Last edited by Aaron Blood; November 17th, 2006, 03:40.
            Sub All_Macros(Optional control As Variant)

            Comment


            • #7
              Re: Highlighting ARRAY formula cells

              This would be a nice one for Conditional Formatting with a Custom Function.

              Code:
              Function Has_Array(Formula_cell As Range) As Boolean
                 Has_Array = Formula_cell.HasArray
              End Function

              Comment


              • #8
                Re: Highlighting ARRAY formula cells

                Originally posted by Dave Hawley
                This would be a nice one for Conditional Formatting with a Custom Function.
                That's a neat idea...
                Last edited by Aaron Blood; November 17th, 2006, 23:19.
                Sub All_Macros(Optional control As Variant)

                Comment


                • #9
                  Re: Highlighting ARRAY formula cells

                  Using Excel's Macro functions

                  Define HasArray
                  refers to : =GET.CELL(49,INDIRECT("rc",FALSE))

                  In CF,

                  Formula Is: =HasArray

                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Highlighting ARRAY formula cells

                    Cool, always more ways that one to skin a cat

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X