Ignoring Error VBA

**mattadams84** · 12-01-2018, 03:09 AM

Hi,

I have got an excel the sheet doing pretty much exactly what i want it to. It basically filters through a data page which contains lots of football matches. Each row on the data sheet is a football match and the columns hold the data specific to that match. I have run in to a bug. Here is the code:

Sub FHTrades()    
Application.ScreenUpdatang = False
    Dim LastRow As Long, fs As Worksheet, ds As Worksheet, x As Long
    Set fs = Sheets("Filters")
    Set ds = Sheets("Data")
    LastRow = ds.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ClearSelections
    For x = 3 To LastRow
        If ds.Cells(x, 40) >= fs.Range("C2") And ds.Cells(x, 41) >= fs.Range("C2") And ds.Cells(x, 42) >= fs.Range("C5") And ds.Cells(x, 43) >= fs.Range("C6") And ds.Cells(x, 44) >= fs.Range("C7") And WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 23).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) <= fs.Range("C8") Then
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "B").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "C").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 4)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "D").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 5)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "E").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 42).Value & "% (" & WorksheetFunction.Round(ds.Cells(x, 40).Value / 100 * ds.Cells(x, 42).Value, 0) & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "F").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 43).Value & "% (" & WorksheetFunction.Round(ds.Cells(x, 41).Value / 100 * ds.Cells(x, 43).Value, 0) & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "G").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 44).Value & "%"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "H").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "I").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 144).Value + ds.Cells(x, 159).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 144).Value + ds.Cells(x, 159).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "J").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 147).Value + ds.Cells(x, 162).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 147).Value + ds.Cells(x, 162).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "K").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 60).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 60).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "L").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 74).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 74).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "M").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 101).Value * (ds.Cells(x, 115).Value / 100) + ds.Cells(x, 102).Value * (ds.Cells(x, 117).Value / 100) + ds.Cells(x, 121).Value * (ds.Cells(x, 135).Value / 100) + ds.Cells(x, 122).Value * (ds.Cells(x, 137).Value / 100)) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value), 2)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "N").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 81)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "O").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 91)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "P").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 82)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 92)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "R").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 83)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "S").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 93)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "T").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 88)
            Sheets("FHSelections").Cells(Sheets("FHSelections").Rows.Count, "U").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 98)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub




Sub SHTrades()
    Application.ScreenUpdating = False
    Dim LastRow As Long, fs As Worksheet, ds As Worksheet, x As Long
    Set fs = Sheets("Filters")
    Set ds = Sheets("Data")
    LastRow = ds.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ClearSelections
    For x = 3 To LastRow
        If ds.Cells(x, 40) >= fs.Range("C2") And ds.Cells(x, 41) >= fs.Range("C2") And ds.Cells(x, 45) >= fs.Range("F5") And ds.Cells(x, 46) >= fs.Range("F6") And ds.Cells(x, 47) >= fs.Range("F7") And WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) <= fs.Range("F8") Then
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "B").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "C").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 4)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "D").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 5)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "E").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 57).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 57).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "F").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 71).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 71).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "G").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 58).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 58).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "H").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 72).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 72).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "I").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 47).Value & "%"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "J").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "K").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 54).Value + ds.Cells(x, 68).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 54).Value + ds.Cells(x, 68).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "L").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 55).Value + ds.Cells(x, 69).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 55).Value + ds.Cells(x, 69).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "M").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 59).Value + ds.Cells(x, 73).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 59).Value + ds.Cells(x, 73).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "N").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 62).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 62).Value & "/" & ds.Cells(x, 40).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "O").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(ds.Cells(x, 76).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 76).Value & "/" & ds.Cells(x, 41).Value & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "P").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 179).Value + ds.Cells(x, 193).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 179).Value + ds.Cells(x, 193).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) / WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) & ")"
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "R").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 101).Value + ds.Cells(x, 102).Value + ds.Cells(x, 121).Value + ds.Cells(x, 122).Value) / WorksheetFunction.Sum(ds.Cells(x, 40).Value + ds.Cells(x, 41).Value), 2)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "S").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 81)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "T").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 91)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "U").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 82)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "V").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 92)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "W").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 83)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "X").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 93)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Y").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 88)
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Z").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 98)
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

This works well for the most part but i am getting an on this line :

Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = WorksheetFunction.Round(WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) / WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) * 100, 0) & "% (" & WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) & "/" & WorksheetFunction.Sum(ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) & ")"

Now, i know why i am getting this error because for one of the matches, the data in the columns that it is trying to calculate is 0. Basically the VBA is trying to do (0+0)/(0+0). Obviously this is going to throw up an error. Is there anyway to get around it this in VBA. So basically if, for one of the matches, where the numbers in those columns are 0 and therefore it tries to calculate with just 0's then instead of doing the calculation it will insert the Value "N/A"...

Here is a link to the file:

https://www.dropbox.com/s/mlwss7s3ue...ters.xlsm?dl=0

Thread: Ignoring Error VBA

Thread Tools

Display

Threaded View

Ignoring Error VBA

Posting Permissions