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

Question

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

score 2 · Answer 1 · answered Sep 15 '22 at 10:41

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.

score 0 · Answer 2 · answered Sep 15 '22 at 09:15

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.

Is useful to know why the code doesn't work and/or share an example file to test your result — Terio, Sep 16 '22 at 10:47

Justin Edwards · Answer 3 · 2022-09-19T13:49:56.103

-1

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

edited Sep 19 '22 at 13:49

answered Sep 15 '22 at 08:29

Justin Edwards

310
1
4
7

Apart from a terrible advice to [use goto](https://stackoverflow.com/q/3517726/11683) where it is not needed, this tells nothing about how to apply this per-row logic to the code that sets a single formula to the entire range at once. – GSerg Sep 15 '22 at 08:34
I must have misunderstood your question. It seems I answered before you were finished editing your question. – Justin Edwards Sep 15 '22 at 08:41
I did not ask the question. My edit of the question was purely cosmetic. The text of the question [was not changed](https://stackoverflow.com/posts/73727836/revisions) compared to the original. – GSerg Sep 15 '22 at 08:43
@GSerge I am try to correct this egregious error on my part and turn this into a quality post. Would you please, please look at this again and give me any advice on how to make this better. I appreciate your correction. – Justin Edwards Sep 19 '22 at 04:22
Now you have the same code as in an [already existing answer](https://stackoverflow.com/a/73729682/11683) posted earlier. That is, the answer itself was posted later, but when it was posted, your answer [contained](https://stackoverflow.com/revisions/73727921/1) different code. – GSerg Sep 19 '22 at 07:48
@GSerge Fair enough. I fiddled around with this some more and developed a unique approach that achieves the same end. I hope somebody finds it useful. – Justin Edwards Sep 19 '22 at 13:47

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

3 Answers3