CODEWITHSUNDEEP
vbaexcel
MtArronK
MtArronK

Reputation: 13

VBA Writing IFERROR to Multiple Cells with For...Next Loop

I am trying to apply "=IFERROR" to a spreadsheet containing over 1000 rows of data. I already came up with a way to make the entries hard-coded. But is there a way to fill the cells with something like "=IFERROR(IFERROR(A1,B1),"")" rather than the value? Below is the hard-coded version:

Sub HardCodeIFERROR()             
    Dim a As Integer, xRecordCount1 As Integer
    Set w(1) = Sheets("ABC")
    xRecordCount1 = w(1).Cells(Rows.Count, 1).End(xlUp).Row

    For a = 1 To xRecordCount1

        w(1).Cells(a, 3).Value = Application.WorksheetFunction.IfError(Application.WorksheetFunction.IfError(Range("A" & a), Range("B" & a)), "")

        Next a
    Exit Sub


End Sub

Thank you in advance for your help!

Upvotes: 0

Views: 254

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

You can instead just use .Formula:

w(1).Cells(a, 3).Formula = "=IFERROR(IFERROR(A" & a & ",B" & a & "),"""")"

Note you can skip the loop and just use a range:

Sub HardCodeIFERROR()
Dim ws1 As Worksheet
Dim a As Integer, xRecordCount1 As Integer
Set ws1 = Sheets("Sheet1")
xRecordCount1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row

With ws1
    .Range(.Cells(1, 3), .Cells(xRecordCount1, 3)).FormulaR1C1 = "=IFERROR(IFERROR(RC[-2],RC[-1]),"""")"
End With

End Sub

Note: Make sure to use the sheet with the Rows.Count whenever you use it, just like you do with Cells() and Range(). Also, I changed the sheet name because I wasn't sure if you intended to do a Sheet Array or not, so I used a more clear (IMO) variable name.

Upvotes: 2

YowE3K
YowE3K

Reputation: 23994

Just use the Formula property:

Sub HardCodeIFERROR()             
    Dim a As Integer, xRecordCount1 As Integer
    'Need to declare the size of the array if you are going to assign worksheets to "w(1)"
    Dim w(1 To 1) As Worksheet
    Set w(1) = Sheets("ABC")
    'Ensure you fully qualify "Rows.Count" by specifying which worksheet you are referring to
    xRecordCount1 = w(1).Cells(w(1).Rows.Count, 1).End(xlUp).Row
    'Apply formula to all cells
    w(1).Range("C1:C" & xRecordCount1).Formula = "=IFERROR(IFERROR(S1,V1),"""")"
End Sub

Upvotes: 1

Related Questions