Array fomulas - modifying them in code

[greencroft]

I am writing code to convert formulas in a worksheet to text by dropping off the initial "=" that they start with.

All seems fine until the code meets an array formula when it throws up an error as you can't change part of an array.

My query is two fold:

1. How do you identify an array formula in the code? I am doing a For Each Next loop that cycles through all elements in the UsedRange of the sheet pulling the value of Cell.Formula into a string variable, then testing to see if it has an "=" sign as its first character and removing it if it does. When doing this the "{" of the array formula seems to evaporate!

2. If the answer is that I have to identify the entire range that the array occupies, is there a way to do this in code? If there is then I can simply put the text of the top formula into a string variable, clear the whole array range then refill with the text in the string variable.

Many thanks in adavance for any tips here.

 

[RoryA]

Something like:

   Dim rngFormulas As Range, rngCell As Range
   On Error Resume Next
   Set rngFormulas = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
   On Error GoTo 0
   If Not rngFormulas Is Nothing Then
      For Each rngCell In rngFormulas.Cells
         With rngCell
         If .HasArray Then
            .CurrentArray.Formula = Mid(.FormulaArray, 2)
         Else
            If Left(.Formula, 1) = "=" Then .Formula = Mid(.Formula, 2)
         End If
         End With
      Next rngCell
   End If

 

[greencroft]

Thanks v much.

The .HasArray and the .CurrentArray bits were just what I needed.

Thanks again.