Issue with adding formula in a cell using VBA using IF

user_18994440 0
Viewed 115

I am trying to make a macro where I add the formula below into a cell and then drag it down the whole column and replicate the same formula in H column

Data where I'd like to enter formulas in column F and H enter image description here

Range("F1").formula = "=IF(ISERROR(MATCH(E1,G:G,0)),"",E1)"
Range("H1").formula = "=IF(ISERROR(MATCH(E1,I:I,0)),"",E1)"
excel-formula vba excel
3 Answers

You need to double up on the quotes, i.e.

Range("F1").formula = "=IF(ISERROR(MATCH(E1,G:G,0)),"""",E1)"
Range("H1").formula = "=IF(ISERROR(MATCH(E1,I:I,0)),"""",E1)"

If you want to enter the formula to multiple cells, however, you're better off using the FormulaR1C1 property, e.g.

   Range("F1:F50").FormulaR1C1 = "=IF(ISERROR(MATCH(RC[-1],C[1],0)),"""",RC[-1])"
   Range("H1:H50").FormulaR1C1 = "=IF(ISERROR(MATCH(RC[-3],C[1],0)),"""",RC[-3])"
user_188247270

Write Formulas

Option Explicit

Sub WriteFormulas()

    Const fRow As Long = 1
    Const ColumnsAddress As String = "F:F,H:H"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim lrCell As Range
    Set lrCell = ws.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious)
    If lrCell Is Nothing Then Exit Sub
    Dim rCount As Long: rCount = lrCell.Row - fRow + 1
    
    With Intersect(ws.Rows(fRow).Resize(rCount), ws.Range(ColumnsAddress))

        Dim cAddress As String: cAddress = .Cells(1).Offset(, -1).Address(0)
        Dim rAddress As String: rAddress = .Areas(1).Offset(, 1).Address(, 0)

        .Formula = "=IF(ISERROR(MATCH(" _
            & cAddress & "," & rAddress & ",0)),""""," & cAddress & ")"

        MsgBox "Formulas written to range '" & .Address(0, 0) & "'.", _
            vbInformation

    End With

End Sub
user_98140690

If there's nothing else under your table, then I would try this:

With ActiveSheet   ' replace with a proper worksheet object
    Intersect(.Range("F:F,H:H"), .UsedRange).FormulaR1C1 = _
        "=IF(ISERROR(MATCH(RC5, C[1], 0)), """", RC5)"   ' RC5 == $E1
End With

If there's already written some formula, then we can use an AutoFill or Copy method:

Interval = "1:" & Range("E1").End(xlDown).Row   ' autofill along data in E 
With Range("F1")
    .Formula = "=IF(ISERROR(MATCH($E1,G:G,0)),"""",$E1)"
    .AutoFill .Rows(Interval)
    .Copy Range("H1").Rows(Interval)
End With
user_149096210
Related