CODEWITHSUNDEEP
vbaexcel
Calin Lencar
Calin Lencar

Reputation: 159

Error Handling to do nothing

I have a macro that does a sumif for columns D:BC, but sprinkled between those columns, there are columns that don't need a sumif and their column header won't be found in the search range, thus resulting in an error.

Is there any way when an error occurs to make it leave the value that was there before? Or make it do nothing?

If i use On Error Resume Next, it will take my last value and populate the range with that value until the next non-error value:

Sub Sum()
Dim result As Variant, n&, u&
n = Worksheets("Sum Worksheet").Cells(Rows.Count, 1).End(xlUp).Row - 1
u = Worksheets("Sum Worksheet").Cells(2, Columns.Count).End(xlToLeft).Column
On Error Resume Next
For J = 4 To u
For i = 2 To n
    result = Application.WorksheetFunction.SumIf(Sheets("Raw Data").Range("BS:BS"), Sheets("Sum Worksheet").Cells(i + 1, 1).Value, Application.WorksheetFunction.Index(Sheets("Raw Data").Range("A:NK"), 1, Application.WorksheetFunction.Match(Sheets("Sum Worksheet").Cells(2, J).Value, Sheets("Raw Data").Range("A1:NK1"), 0)))
    Sheets("Sum Worksheet").Cells(i + 1, J) = result
Next i
Next J
End Sub

Thanks in advance.

Upvotes: 1

Views: 4934

Answers (1)

Vityata
Vityata

Reputation: 43585

There are very few cases, when On Error Resume Next is ok to stay in the code, thus probably it is always a good idea to remove it. So, delete On Error Resume Next and then edit the nested loop to this:

For J = 4 To u
    For i = 2 To n
        result = Application.WorksheetFunction.SumIf(Sheets("Raw Data")...
        If IsError(result) Then
            'write your ideas
            'or leave it empty, it will do nothing
        Else
            Sheets("Sum Worksheet").Cells(i + 1, J) = result
        End If
    Next i
Next J

Upvotes: 4

Related Questions