Formula in VBA, putting a formula in a range of cells but excluding certain cells

Question

I have code that will go to a range and insert a formula all the way down. The below code will go to range N17:N160 and insert a relative formula.

Sub Macro9()
    Range("n17:n160").FormulaR1C1 = "=IF(RC[-11]="""","""",IFERROR(VLOOKUP(RC[-11],R17C42:R160C53,7,FALSE),""""))"
End Sub

It works. However, I need to add a level where it looks at range B17:B160 and any cell in that range that has a "Y" in it will not update with the formula, whereas any one without a "Y", will update. Basically if and row has a Y in column B, don't put the formula in, for every other cell in the range, do put the formula in.

Thanks

First time posting, sorry if I didn't format everything correctly.

Answer 1

You'll need to build a range reference that meest your criteria for adding the Formula,

Something like

Sub Macro9()
    Dim rng As Range, rngFiltered As Range
    Dim dat As Variant
    Dim idx As Long
    
    Set rng = Range("n17:n160")
    dat = rng.Offset(0, -12).Value2 ' Column B data
    rng.ClearContents ' Clear existing data from range
    For idx = 1 To UBound(dat)
        If dat(idx, 1) <> "y" Then
            If rngFiltered Is Nothing Then
                Set rngFiltered = rng.Cells(idx, 1)
            Else
                Set rngFiltered = Application.Union(rngFiltered, rng.Cells(idx, 1))
            End If
        End If
    Next
    rngFiltered.FormulaR1C1 = "=IF(RC[-11]="""","""",IFERROR(VLOOKUP(RC[-11],R17C42:R160C53,7,FALSE),""""))"
End Sub