Using IfError function in VBA

[CarolynS]

I have the follwoing code to add in an IfError formula to column 8 of my worksheet

    FinalRow = Worksheets("CompsByCourse").Cells(Rows.Count, 1).End(xlUp).Row
    
    'On Error Resume Next
    
    For p = 5 To FinalRow
    
    Worksheets("CompsByCourse").Cells(p, 8) = Application.WorksheetFunction.IfError(Worksheets("CompsByCourse").Cells(p, 7) * Worksheets("CompsByCourse").Cells(p, 2), """0""")
    
    Next p
    'On Error GoTo 0

The formula is just multiplying 2 other columns but i use IfError as some of the data is non numerical and rather than causing an error to be displayed I would like a value of 0. This would work if i was typing the formula directly in to the worksheet, however, each time the code gets to the cells that don't contain numbers it errors. I could use On Error Resume Next (as shown in code above) but i would rather have not have blanks in the column.

It seems like there's no point in using the worksheet function IfError and rather I should be putting an "If IsError then" in to my code? I did try to do this but I didn't know how to fit it in around my For, Next loop and how to make it return 0 for cells that error?

Any help greatly appreciated

Thanks

 

[JackDanIce]

Instead of the above, try:

    FinalRow = Worksheets("CompsByCourse").Cells(Rows.Count, 1).End(xlUp).Row
    With Worksheets("CompsByCourse").Cells(5, 8).Resize(FinalRow - 4)
        .FormulaR1C1 = "=IFERROR(RC[-1]*RC[-6],0)"
        .FillDown
        .Value = .Value
    End With

 

[CarolynS]

That does the job. Thank you

 

[JackDanIce]

Glad it's resolved and you're welcome.