CODEWITHSUNDEEP
excelvbaif-statement
Rachel Taylor
Rachel Taylor

Reputation: 57

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

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),"""")"

Upvotes: 1

Views: 120

Answers (3)

Justin Edwards
Justin Edwards

Reputation: 482

You are correct. As already noted in another answer to this question, you can limit the application of your formula using If not [Range] Is Nothing, but here is an alternative approach that produces a list of all visible cell ranges within a specific column, and then simply loops through those ranges to apply the formula:

Sub loFormulaLoop()
Application.ScreenUpdating = False 'added to make the code run faster

'Explicitly defining everything saves time in the event of changes
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim lo As ListObject
Dim colorColumn As Integer
Dim formulaColumn As Integer
Dim cellList As String
Dim arrayList As Variant
Dim iteration As Integer
Set ws = wb.Sheets("RedaData")
Set lo = ws.ListObjects("Study_Setup")
colorColumn = 31
formulaColumn = 30

'Apply Filter
lo.Range.AutoFilter Field:=colorColumn, Criteria1 _
:=RGB(255, 255, 204), Operator:=xlFilterCellColor

'Get every range in the formula column that is visible and apply the formula
cellList = CStr(lo.ListColumns(formulaColumn).Range.SpecialCells(xlCellTypeVisible).Address)
arrayList = Split(cellList, ",")
For iteration = 1 To UBound(arrayList)
    ws.Range(arrayList(iteration)).Select
    Selection.Formula = "=IFERROR(INDEX(C[-29]:C[-9],MATCH(1,(C[-26]=RC[-29])*(C[-29]=RC[-26]),0),5),"""")"
Next iteration

Application.ScreenUpdating = True 'Display results
End Sub

Upvotes: -1

Ike
Ike

Reputation: 13024

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.

Upvotes: 2

Terio
Terio

Reputation: 507

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.

Upvotes: 0

Related Questions