How to use the If statement to bypass a line of code-CodePudding

How can I bypass the Selection.FormulaArray code if on filtering a column the rows are empty, I don't want it to add the formula just skip to the next section in the code. I assume it is an if statement but, not sure how to write it.

ActiveSheet.ListObjects("Study_Setup").Range.AutoFilter Field:=31, Criteria1 _
:=RGB(255, 255, 204), Operator:=xlFilterCellColor
    
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 30).Select

Selection.FormulaArray = _
    "=IFERROR(INDEX(RedaData!C[-29]:C[-9],MATCH(1,(RedaData!C[-26]=RC[-29])* 
(RedaData!C[-29]=RC[-26]),0),5),"""")"

CodePudding user response：

As you are using a listobject you should make use of the benefits :-)

E.g. you can use the column names instead of counting the columns. To use my example you have to replace "ColumnWithColor" with the name of your column 31, and "ColumnFormula" with the name of column 30

Sub addFormula()

Dim lo As ListObject
Set lo = ActiveSheet.ListObjects("Study_Setup")

With lo
    .Range.AutoFilter field:=.ListColumns("ColumnWithColor").Index, Criteria1:=RGB(255, 255, 204), Operator:=xlFilterCellColor
End With

Dim rg As Range
On Error Resume Next ' in case there are no visible cells
    Set rg = lo.ListColumns("ColumnFormula").DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Not rg Is Nothing Then
    rg.Formula = "=XXX"    'put your formula here
End If

lo.AutoFilter.ShowAllData

End Sub

This solution is also safe if you have multiple listobjects on one sheet with different filters.

CodePudding user response：

Try this:

Dim rngS As Range

    ActiveSheet.ListObjects("Study_Setup").Range.AutoFilter Field:=31, Criteria1:=RGB(255, 255, 204), Operator:=xlFilterCellColor
    
    Set rngS = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
    If Not (rngS.Areas.Count = 1 And rngS.Rows.Count = 1) Then
        ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 30).FormulaArray = "=IFERROR(INDEX(RedaData!C[-29]:C[-9],MATCH(1,(RedaData!C[-26]=RC[-29])* (RedaData!C[-29]=RC[-26]),0),5),"""")"
    End If
    Set rngS = Nothing

This check if only the headers are visible and if not, write your formula.

Bye.