add excel formula if/or statement to vba code

Question

I have this vba code that create a list from another list. What I would like is to add this formula on every row of my new list on column E :

I want to add the formula to this vba code:

Sub m()
Dim c As Range
ActiveWorkbook.RefreshAll
Range("A:E").Delete
For Each c In Sheets("Feuil1").Range("b2:b300")
    If c.DisplayFormat.Interior.Color = vbYellow Then
        Sheets("Feuil3").Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = c.EntireRow.Value
    End If
Next c
End Sub

I just dont know how to link them.. thank you

`Sheets("Feuil3").Range("E2:E300").Formula = "=IF(OR(D2=""TARM01"",D2 = ""BOUM34"",D2=""LESB01""), ""true"",""false"")"` — Scott Craner, Apr 21 '21 at 15:34
It work great but it should stop generating the formula at the end of my new list. In this case right now, the formula is looping forever ... @ScottCraner — Chadi N, Apr 21 '21 at 17:21
that line should not be in the loop. It should be the last line before the `End Sub` — Scott Craner, Apr 21 '21 at 17:37
sorry, i didnt meant looping forever, I wanted to say it goes from E2:E300 even when the new list is only 20 rows long. I would like to match the formula with how many rows are generated in the new list by the loop. — Chadi N, Apr 21 '21 at 17:49
Then you will need to find the end of the data. See: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba for examples on how to do that. Then you would just do something like: `Sheets("Feuil3").Range("E2:E" & lastrow).Formula =...` — Scott Craner, Apr 21 '21 at 17:52
Can't I just say something like "if D cell <> "" then... run the formula" ? — Chadi N, Apr 21 '21 at 18:00
sure, but make sure you change the row reference in the formula. — Scott Craner, Apr 21 '21 at 18:02

score 1 · Accepted Answer · answered Apr 21 '21 at 18:40

Formula to Range

The implementation of a (one-cell) range variable (dCell) makes the code more readable and will make it easy to determine the last row for the formula.

Option Explicit

Sub m()
    ActiveWorkbook.RefreshAll
    Range("A:E").Delete
    Dim dCell As Range
    Set dCell = Sheets("Feuil3").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Dim c As Range
    For Each c In Sheets("Feuil1").Range("B2:B300").Cells
        If c.DisplayFormat.Interior.Color = vbYellow Then
            dCell.EntireRow.Value = c.EntireRow.Value
            Set dCell = dCell.Offset(1)
        End If
    Next c
    Sheets("Feuil3").Range("E2:E" & dCell.Row - 1).Formula = "=IF(OR(D2" _
        & "=""TARM01"",D2=""BOUM34"",D2=""LESB01""),""true"",""false"")"
End Sub

add excel formula if/or statement to vba code

1 Answers1

Formula to Range