0

When changing the value (dropdown) of cell B12, cell B13 should mention "Please Select..." and all cells in range A16:N20 should be cleared but not the formulas (each cell in the range contains index/match formulas linked to cells B12, B13 and others...). This is the current code which does not work...

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$12" Then

        With Application
            .DisplayStatusBar = False
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With

    Range("B13").Value = "Please Select..."
    Range("A16:N20").SpecialCells(xlCellTypeConstants, 23).ClearContents

        With Application
            .Calculation = xlCalculationAutomatic
            .EnableEvents = True
            .ScreenUpdating = True
            .DisplayStatusBar = True
        End With
    End If
End Sub

Also Excel is very slow having to update all these index/match formulas - is there a way in vba to make it faster?

Dominique
  • 16,450
  • 15
  • 56
  • 112
Debby
  • 3
  • 1

1 Answers1

0
Dim r as Range

for each r in Range("A16:N20").Cells
  if WorksheetFunction.isFormula(r) = "" then
    r.clearcontents
  end if
next r

As you see, you can use the IsFormula() worksheet function, in order to solve this.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • 1
    There is no `WorksheetFunction.FormulaText` method. `WorksheetFunction.IsFormula` might be what you meant? – BigBen Apr 20 '20 at 15:50
  • Oops, sorry. Yes, it is. – Dominique Apr 20 '20 at 15:55
  • Thank you, this indeed works fine when I use it in a separate sub. However, I would like to integrate it in the existing sub so that it works when the dropdown in cell B12 changes. Is this possible? – Debby Apr 23 '20 at 08:57
  • @Debby: I adapted my answer, based on this post "https://stackoverflow.com/questions/3875415/loop-through-each-cell-in-a-range-of-cells-when-given-a-range-object". Can you check if it works? – Dominique Apr 23 '20 at 09:23