Consulting

Results 1 to 9 of 9

Thread: Ignoring Error VBA

  1. #1

    Ignoring Error VBA

    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

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    Well, all you want to do is to check your denominator is 0. Use the IIF command to do so.
    IIF(your line of code=0,”n/a”,do your calculation)

  3. #3
    Thanks for the reply. Unless i have added it wrong, i am still getting an error

    Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = IIf(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) = 0, "N/A", 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) & ")")

  4. #4
    How about
                If ds.Cells(x, 229).Value = 0 Or ds.Cells(x, 243).Value = 0 Then Z = "N/A" Else Z = 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, "Q").End(xlUp).Offset(1, 0).Value = Z

  5. #5
    Another variation

       
        If (ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) = 0 Then
            Sheets("SHSelections").Cells(Sheets("SHSelections").Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = "N/A"
        Else
            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) & ")"
        End If

  6. #6
    Btw, have you considered doing without the WorksheetFunction.Round and WorksheetFunction.Sum functions? There is already a VBA round function, and the way you are using WorksheetFunction.Sum

    WorksheetFunction.Sum(ds.Cells(x, 64).Value + ds.Cells(x, 78).Value)
    produces the same result as just adding the cell values

    ds.Cells(x, 64).Value + ds.Cells(x, 78).Value

    Example:
    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 Round((ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) <= fs.Range("F8") Then
                With Sheets("SHSelections")
                    .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)
                    .Cells(.Rows.Count, "C").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 4)
                    .Cells(.Rows.Count, "D").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 5)
                    .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0).Value = Round(ds.Cells(x, 57).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 57).Value & "/" & ds.Cells(x, 40).Value & ")"
                    .Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0).Value = Round(ds.Cells(x, 71).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 71).Value & "/" & ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "G").End(xlUp).Offset(1, 0).Value = Round(ds.Cells(x, 58).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 58).Value & "/" & ds.Cells(x, 40).Value & ")"
                    .Cells(.Rows.Count, "H").End(xlUp).Offset(1, 0).Value = Round(ds.Cells(x, 72).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 72).Value & "/" & ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "I").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 47).Value & "%"
                    .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 257).Value + ds.Cells(x, 275).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & ds.Cells(x, 257).Value + ds.Cells(x, 275).Value & "/" & ds.Cells(x, 40).Value + ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 54).Value + ds.Cells(x, 68).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & ds.Cells(x, 54).Value + ds.Cells(x, 68).Value & "/" & ds.Cells(x, 40).Value + ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "L").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 55).Value + ds.Cells(x, 69).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & ds.Cells(x, 55).Value + ds.Cells(x, 69).Value & "/" & ds.Cells(x, 40).Value + ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "M").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 59).Value + ds.Cells(x, 73).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & ds.Cells(x, 59).Value + ds.Cells(x, 73).Value & "/" & ds.Cells(x, 40).Value + ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "N").End(xlUp).Offset(1, 0).Value = Round(ds.Cells(x, 62).Value / ds.Cells(x, 40).Value * 100, 0) & "% (" & ds.Cells(x, 62).Value & "/" & ds.Cells(x, 40).Value & ")"
                    .Cells(.Rows.Count, "O").End(xlUp).Offset(1, 0).Value = Round(ds.Cells(x, 76).Value / ds.Cells(x, 41).Value * 100, 0) & "% (" & ds.Cells(x, 76).Value & "/" & ds.Cells(x, 41).Value & ")"
                    .Cells(.Rows.Count, "P").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 179).Value + ds.Cells(x, 193).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value) * 100, 0) & "% (" & ds.Cells(x, 179).Value + ds.Cells(x, 193).Value & "/" & ds.Cells(x, 40).Value + ds.Cells(x, 41).Value & ")"
                    If ds.Cells(x, 229).Value + ds.Cells(x, 243).Value = 0 Then
                        .Cells(.Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = "N/A"
                    Else
                        .Cells(.Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 64).Value + ds.Cells(x, 78).Value) / (ds.Cells(x, 229).Value + ds.Cells(x, 243).Value) * 100, 0) & "% (" & ds.Cells(x, 64).Value + ds.Cells(x, 78).Value & "/" & ds.Cells(x, 229).Value + ds.Cells(x, 243).Value & ")"
                    End If
                    .Cells(.Rows.Count, "R").End(xlUp).Offset(1, 0).Value = Round((ds.Cells(x, 101).Value + ds.Cells(x, 102).Value + ds.Cells(x, 121).Value + ds.Cells(x, 122).Value) / (ds.Cells(x, 40).Value + ds.Cells(x, 41).Value), 2)
                    .Cells(.Rows.Count, "S").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 81)
                    .Cells(.Rows.Count, "T").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 91)
                    .Cells(.Rows.Count, "U").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 82)
                    .Cells(.Rows.Count, "V").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 92)
                    .Cells(.Rows.Count, "W").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 83)
                    .Cells(.Rows.Count, "X").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 93)
                    .Cells(.Rows.Count, "Y").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 88)
                    .Cells(.Rows.Count, "Z").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 98)
                End With
            End If
        Next x
        Application.ScreenUpdating = True
    End Sub

  7. #7
    Thanks for your replies. I have implemented the code and it works perfectly. I assumed that i needed to use worksheet.function so i did. I have now got rid of that too.

    I have another question, slightly off topic, As you can see, i define which column the data gets put in. Is there anyway to do this dynamically, so instead of defining that something will go in column Q or F etc... could i use code to find the next available column? This means that if i needed to add a column, lets say before column C, I then dont have to go the VBA code to update where the data is put...

  8. #8
    That depends on your data structure. One way is to use a header row. If you have a row at the top that contains the column labels, you can scan that to build a dynamic location reference for each column you are interested in. That way you can move the columns around or add new columns without disturbing existing VBA code. Let's say row 1 is a header row and cell column B contains the goal data. B1 contains the column label "Goals". The basic approach is to define a variable to hold the column number, and populate it each time you run your subroutine

    Dim GoalDataCol as long
    
    GoalDataCol = GetColumnNoFromHeaderRow("Goals", 1, 1, Sheets("SHSelections"))
    then instead of this:

                   .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)
    do this

                   .Cells(.Rows.Count, GoalDataCol).End(xlUp).Offset(1, 0).Value = ds.Cells(x, 1)

    Other columns are handled similarly.


    ''' Scans a header row to find a particular column label string. Returns the column number.
    Function GetColumnNoFromHeaderRow(StringToFind As String, HeaderRowNo As Long, StartColumn As Long, Optional WS As Worksheet) As Long
        Dim ColNo As Long
        Dim R As Range, HR As Range
        Dim FoundMatch As Boolean
        Dim ColumnLabel As String
    
        If WS Is Nothing Then
            Set WS = ActiveSheet
        End If
    
        With WS
           Set HR = .Range(.Cells(HeaderRowNo, StartColumn), .Cells(HeaderRowNo, .Columns.Count).End(xlToLeft))
        End With
    
        For Each R In HR
            ColumnLabel = Trim(R.Value)
            FoundMatch = (ColumnLabel = StringToFind)
            If FoundMatch Then
                ColNo = R.Column
                Exit For
            End If
        Next R
    
        If FoundMatch Then
            GetColumnNoFromHeaderRow = ColNo
        Else
            GetColumnNoFromHeaderRow = 0
        End If
    End Function
    Last edited by rlv; 12-02-2018 at 12:53 PM. Reason: code correction

  9. #9
    Ok i will give it a bash. thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •