1

I am using a formula to indent text in column "D" based on values in column "C" and have been using it in VBA to format worksheets. It looks like this: =setindent(D2,C2). Until recently, it has worked well, but now it doesn't. It looks like Microsoft has started to force "implicit intersection operators" into formulas (adding the @: =@setindent(D2,C2). It seems to have neutered my formula where it returns "#Value!". If I open one of the cells with the formula in edit mode and then hit "Enter" it changes to a number and the formula works on the targeted cell text. How do I resolve this in VBA?

Here is the formula:

Function SetIndent(z As Range, ByVal Level As Long) As Variant


    Dim celldent As Range
 
    SetIndent = IIf(Level < 0, "Min is 0!", IIf(Level > 10, "Max is 10!", Level))
    If Level < 0 Then Level = 0 Else If Level > 10 Then Level = 10
 
    For Each celldent In z
        With celldent
            If Level - .IndentLevel Then .InsertIndent Level - .IndentLevel
        End With
    Next celldent
End Function

. . . and here is the VBA copying the formula

 'Format the Name (Column D) to indent per the Outline Level value in Column C
'See Module 16 for the Function: SetIndent
    Range("AB2").Select
    ActiveCell.Formula = "=SetIndent(D2,C2)"
    Range("AB2").Copy Range("$AB$3:AB" & lastRow)
  ' Range("$AB2:AB" & lastRow).Clear

Thanks,

Tim

Tim Z
  • 23
  • 4
  • 3
    Perhaps try using `Formula2` – Rory Sep 22 '20 at 15:16
  • FYI see post referring to the **implicit intersection operator** at [How to add dynamic array formula](https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula/61138478#61138478) – T.M. Sep 22 '20 at 18:42
  • TM and Rory, thanks for pointing out the Formula2 option and the info on IIO's. I tried Formula2 and still get the same result: #VALUE! until I edit the cell. The formula works when I manually copy and paste it, so I don't know what is not happening when the same VBA operation doesn't work.? – Tim Z Sep 24 '20 at 14:28

1 Answers1

1

Try to enter the formulae directly into the entire range using .Formula2 instead of .Formula

        Range("AB2:AB" & lastRow).Formula2 = "=SetIndent(D2,C2)"

So you can avoid unnecessary selecting/activating, too.

Furthermore I'd recommend to fully qualify your range references, e.g. via Sheet1.Range("AB2:AB" & lastRow).Formula2 using the worksheet's Code(Name) or ThisWorkbook.Worksheets("Sheet1")... using the Excel sheet's name string identifyer :-)

T.M.
  • 9,436
  • 3
  • 33
  • 57
  • Did you try my answer? If helpful, feel free to mark it as accepted (acceptance is indicated by a colored checkmark next to the answer) - c.f. ["Someone answers"](https://stackoverflow.com/help/someone-answers) – T.M. Sep 30 '20 at 06:33
  • thanks for your advice on the more efficient methods. I will need to update all my modules with your suggestions to improve performance. Unfortunately, the modifications haven't helped with my current problem. The function is not working when applied to the range of cells with VBA. Still getting the #VALUE! in each cell. – Tim Z Oct 06 '20 at 15:51