Ignoring Error VBA

[mattadams84]

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/mlwss7s3ueqx6o7/Filters.xlsm?dl=0

[JKwan]

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)

[mattadams84]

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) & ")")

[Fluff]

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

[rlv]

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

[rlv]

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

[mattadams84]

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...

[rlv]

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

[mattadams84]

Ok i will give it a bash. thanks