Help to fix & modify Macro Code?

B

bjbalmforth

Board Regular
Joined
Jul 21, 2005
Messages
117
The code that I have is susposed to look at sheet A Grade Rd1 and then compare names in column A in sheets A Grade Rd2 & A Grade Rd3, if the names appear in those sheet then extract data to Results sheet, the same name must appear in at least 2 sheets in any order, ie: sheet 2 &3 or 1 & 2.

The Macro that I have only looks at the A Grade Rd1 then takes the data from say row 2 on the 3 sheets regardless if the name is the same.

Also on the result sheet in columns Q to U there is data on the last row that is unrelated to the other sheets?

Other than these 2 faults the Macro works fine, what is susposed to happen is that if a name match is found in at least 2 of the 3 sheets then the data is extracted as follows, name in column A on results sheet and the data from A Grade Rd1 goes in colums:
B to F
From A Grade Rd2 goes in columns: G to K

Data from A Grade Rd3 goes in columns L to P

This data is then calculated and sorted in columns Q to U, below is a copy of the Macro and how the sheets look, I have only included A Grade Rd1 as the other sheets are the same except that the names may be in a different order.

Option Explicit

Private Sub Worksheet_Activate()
Dim sh As Worksheet

Application.ScreenUpdating = False

With Me
'clear out any existing data
.Rows("2:200").ClearContents

'*****Change from B Grade
PostData "A Grade Rd1", 2
PostData "A Grade Rd2", 7
PostData "A Grade Rd3", 12

.Range("Q2:U2").Formula = "=SUMPRODUCT(SMALL(IF(N(OFFSET(B2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(B2:$P2 ,0,{0,5,10},1,1))),{1,2}))"
.Range("Q2:U2").AutoFill .Range("Q2:U2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row - 1)




.Range("A2:Z2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).Sort _
Key1:=.Range("F2"), _
Order1:=xlAscending, _
Key2:=.Range("E2"), _
Order2:=xlAscending, _
Key3:=.Range("D2"), _
Order3:=xlDescending
.Range("A2:Z2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).Sort _
Key1:=.Range("B2"), _
Order1:=xlAscending, _
Key2:=.Range("C2"), _
Order2:=xlAscending

End With

Application.ScreenUpdating = True

End Sub

Private Sub PostData(SheetName As String, StartCol As Long)
Dim sh As Worksheet
Dim ce As Range
Dim iRow As Long

On Error Resume Next
Set sh = Worksheets(SheetName)
On Error GoTo 0

If Not sh Is Nothing Then
For Each ce In sh.Range("A2").Resize(sh.Cells(Rows.Count, "A").End(xlUp).Row)
iRow = 0

On Error Resume Next
'***** Change to sh.range
iRow = Application.Match(ce.Value, sh.Range("A:A"), 0)
On Error GoTo 0
If iRow = 0 Then
If Me.Range("A2").Value = "" Then
iRow = 2
Else
iRow = Me.Range("A1").End(xlDown).Row + 1
End If
Me.Cells(iRow, "A").Value = ce.Value
End If
'***** Next line added
Me.Cells(iRow, "A").Value = ce.Value
ce.Offset(0, 1).Resize(1, 5).Copy Me.Cells(iRow, StartCol)
Rows(2).Copy
Rows(ce.Row).PasteSpecial Paste:=xlPasteFormats
Next ce
End If
End Sub
Book1
ABCDEF
1A Grade Round 1
2N Miller189111
3F Fontane7034334
4R Walker7135434
5Barry J7236444
6R Jones7638354
7M Smith8241345
8H Franks10148336
9R Tallon10253447
10P Dye11139337
11
12
Sheet1
<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=22><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows XP</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=22><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb605117><INPUT onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' type=button value="Copy Formula" name=btCb942116></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=22><TABLE border=0><TBODY><TR><FORM name=formFb202339><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name=sltNb447362><OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(B2:$P2,0,{0,5,10},1,1))),{1,2})) selected>Q2<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(C2:$P2,0,{0,5,10},1,1))),{1,2}))>R2<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(D2:$P2,0,{0,5,10},1,1))),{1,2}))>S2<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(E2:$P2,0,{0,5,10},1,1))),{1,2}))>T2<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(F2:$P2,0,{0,5,10},1,1))),{1,2}))>U2<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B3:$P3,0,{0,5,10},1,1))=0,999,N(OFFSET(B3:$P3,0,{0,5,10},1,1))),{1,2}))>Q3<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C3:$P3,0,{0,5,10},1,1))=0,999,N(OFFSET(C3:$P3,0,{0,5,10},1,1))),{1,2}))>R3<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D3:$P3,0,{0,5,10},1,1))=0,999,N(OFFSET(D3:$P3,0,{0,5,10},1,1))),{1,2}))>S3<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E3:$P3,0,{0,5,10},1,1))=0,999,N(OFFSET(E3:$P3,0,{0,5,10},1,1))),{1,2}))>T3<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F3:$P3,0,{0,5,10},1,1))=0,999,N(OFFSET(F3:$P3,0,{0,5,10},1,1))),{1,2}))>U3<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B4:$P4,0,{0,5,10},1,1))=0,999,N(OFFSET(B4:$P4,0,{0,5,10},1,1))),{1,2}))>Q4<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C4:$P4,0,{0,5,10},1,1))=0,999,N(OFFSET(C4:$P4,0,{0,5,10},1,1))),{1,2}))>R4<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D4:$P4,0,{0,5,10},1,1))=0,999,N(OFFSET(D4:$P4,0,{0,5,10},1,1))),{1,2}))>S4<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E4:$P4,0,{0,5,10},1,1))=0,999,N(OFFSET(E4:$P4,0,{0,5,10},1,1))),{1,2}))>T4<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F4:$P4,0,{0,5,10},1,1))=0,999,N(OFFSET(F4:$P4,0,{0,5,10},1,1))),{1,2}))>U4<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B5:$P5,0,{0,5,10},1,1))=0,999,N(OFFSET(B5:$P5,0,{0,5,10},1,1))),{1,2}))>Q5<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C5:$P5,0,{0,5,10},1,1))=0,999,N(OFFSET(C5:$P5,0,{0,5,10},1,1))),{1,2}))>R5<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D5:$P5,0,{0,5,10},1,1))=0,999,N(OFFSET(D5:$P5,0,{0,5,10},1,1))),{1,2}))>S5<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E5:$P5,0,{0,5,10},1,1))=0,999,N(OFFSET(E5:$P5,0,{0,5,10},1,1))),{1,2}))>T5<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F5:$P5,0,{0,5,10},1,1))=0,999,N(OFFSET(F5:$P5,0,{0,5,10},1,1))),{1,2}))>U5<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B6:$P6,0,{0,5,10},1,1))=0,999,N(OFFSET(B6:$P6,0,{0,5,10},1,1))),{1,2}))>Q6<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C6:$P6,0,{0,5,10},1,1))=0,999,N(OFFSET(C6:$P6,0,{0,5,10},1,1))),{1,2}))>R6<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D6:$P6,0,{0,5,10},1,1))=0,999,N(OFFSET(D6:$P6,0,{0,5,10},1,1))),{1,2}))>S6<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E6:$P6,0,{0,5,10},1,1))=0,999,N(OFFSET(E6:$P6,0,{0,5,10},1,1))),{1,2}))>T6<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F6:$P6,0,{0,5,10},1,1))=0,999,N(OFFSET(F6:$P6,0,{0,5,10},1,1))),{1,2}))>U6<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B7:$P7,0,{0,5,10},1,1))=0,999,N(OFFSET(B7:$P7,0,{0,5,10},1,1))),{1,2}))>Q7<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C7:$P7,0,{0,5,10},1,1))=0,999,N(OFFSET(C7:$P7,0,{0,5,10},1,1))),{1,2}))>R7<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D7:$P7,0,{0,5,10},1,1))=0,999,N(OFFSET(D7:$P7,0,{0,5,10},1,1))),{1,2}))>S7<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E7:$P7,0,{0,5,10},1,1))=0,999,N(OFFSET(E7:$P7,0,{0,5,10},1,1))),{1,2}))>T7<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F7:$P7,0,{0,5,10},1,1))=0,999,N(OFFSET(F7:$P7,0,{0,5,10},1,1))),{1,2}))>U7<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B8:$P8,0,{0,5,10},1,1))=0,999,N(OFFSET(B8:$P8,0,{0,5,10},1,1))),{1,2}))>Q8<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C8:$P8,0,{0,5,10},1,1))=0,999,N(OFFSET(C8:$P8,0,{0,5,10},1,1))),{1,2}))>R8<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D8:$P8,0,{0,5,10},1,1))=0,999,N(OFFSET(D8:$P8,0,{0,5,10},1,1))),{1,2}))>S8<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E8:$P8,0,{0,5,10},1,1))=0,999,N(OFFSET(E8:$P8,0,{0,5,10},1,1))),{1,2}))>T8<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F8:$P8,0,{0,5,10},1,1))=0,999,N(OFFSET(F8:$P8,0,{0,5,10},1,1))),{1,2}))>U8<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B9:$P9,0,{0,5,10},1,1))=0,999,N(OFFSET(B9:$P9,0,{0,5,10},1,1))),{1,2}))>Q9<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C9:$P9,0,{0,5,10},1,1))=0,999,N(OFFSET(C9:$P9,0,{0,5,10},1,1))),{1,2}))>R9<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D9:$P9,0,{0,5,10},1,1))=0,999,N(OFFSET(D9:$P9,0,{0,5,10},1,1))),{1,2}))>S9<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E9:$P9,0,{0,5,10},1,1))=0,999,N(OFFSET(E9:$P9,0,{0,5,10},1,1))),{1,2}))>T9<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F9:$P9,0,{0,5,10},1,1))=0,999,N(OFFSET(F9:$P9,0,{0,5,10},1,1))),{1,2}))>U9<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(B10:$P10,0,{0,5,10},1,1))=0,999,N(OFFSET(B10:$P10,0,{0,5,10},1,1))),{1,2}))>Q10<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(C10:$P10,0,{0,5,10},1,1))=0,999,N(OFFSET(C10:$P10,0,{0,5,10},1,1))),{1,2}))>R10<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(D10:$P10,0,{0,5,10},1,1))=0,999,N(OFFSET(D10:$P10,0,{0,5,10},1,1))),{1,2}))>S10<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(E10:$P10,0,{0,5,10},1,1))=0,999,N(OFFSET(E10:$P10,0,{0,5,10},1,1))),{1,2}))>T10<OPTION value==SUMPRODUCT(SMALL(IF(N(OFFSET(F10:$P10,0,{0,5,10},1,1))=0,999,N(OFFSET(F10:$P10,0,{0,5,10},1,1))),{1,2}))>U10</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==SUMPRODUCT(SMALL(IF(N(OFFSET(B2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(B2:$P2,0,{0,5,10},1,1))),{1,2})) name=txbFb150492></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>A</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>F</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>G</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>H</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>I</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>J</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>K</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>L</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>M</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>N</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>O</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>P</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>Q</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>R</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>S</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>T</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>U</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>1</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>2</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">N Miller</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">18</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">18</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">18</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">36</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">18</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">2</FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>3</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">Barry J</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">71</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">35</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">72</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">72</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">143</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">71</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">8</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">7</FONT></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">8</FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>4</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">R Jones</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">72</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">80</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">72</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">144</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">72</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">8</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">8</FONT></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">8</FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>5</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: left">R Walker</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">76</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">38</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">82</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">73</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">36</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-WEIGHT: bold; FONT-SIZE: 12pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 1.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 0.5
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Hi

Slightly different approach using some of your code. It is in a general module and assumes that the data is in sheet1, 2 and 3 with sheet4 being the output summary sheet.

Code: 
Sub aaa()
 With Sheets("sheet4")
  lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
  .Rows("1:" & lastrow).ClearContents
 End With

 Sheets("sheet1").Select

 For Each ce In Range("a2:a" & Cells(Rows.Count, "A").End(xlUp).Row)
  outrow = Sheets("sheet4").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
  If WorksheetFunction.CountIf(Sheets("sheet2").Range("a:a"), ce) > 0 Then
   Range(ce, ce.Offset(0, 5)).Copy Destination:=Sheets("sheet4").Cells(outrow, "A")
   datarow = WorksheetFunction.Match(ce, Sheets("sheet2").Range("a:a"), 0)
   Sheets("sheet2").Range("b" & datarow & ":F" & datarow).Copy Destination:=Sheets("sheet4").Range("G" & outrow)
  End If
  If WorksheetFunction.CountIf(Sheets("sheet3").Range("a:a"), ce) > 0 Then
   Range(ce, ce.Offset(0, 5)).Copy Destination:=Sheets("sheet4").Cells(outrow, "A")
   datarow = WorksheetFunction.Match(ce, Sheets("sheet3").Range("a:a"), 0)
   Sheets("sheet3").Range("b" & datarow & ":F" & datarow).Copy Destination:=Sheets("sheet4").Range("L" & outrow)
  End If
 Next ce
 
 With Sheets("sheet4")
  .Range("Q2:U2").Formula = "=SUMPRODUCT(SMALL(IF(N(OFFSET(B2:$P2,0,{0,5,10},1,1))=0,999,N(OFFSET(B2:$P2 ,0,{0,5,10},1,1))),{1,2}))"
  .Range("Q2:U2").AutoFill .Range("Q2:U2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row - 1)
  .Range("A2:Z2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).Sort _
   Key1:=.Range("F2"), _
   Order1:=xlAscending, _
   Key2:=.Range("E2"), _
   Order2:=xlAscending, _
   Key3:=.Range("D2"), _
   Order3:=xlDescending
  .Range("A2:Z2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row).Sort _
   Key1:=.Range("B2"), _
   Order1:=xlAscending, _
   Key2:=.Range("C2"), _
   Order2:=xlAscending

 End With
End Sub


HTH

Tony
 
Upvote 0
Thanks for the help on this Tony it works fantastic when there is data in the 3 sheets, but if there is no data in the sheets ie: no A Grade players and you activate the sheet A Grade Rd1 it comes up with a run time error 1004, below is the high lighted line.

.Range("Q2:U2").AutoFill .Range("Q2:U2").Resize(.Cells(Rows.Count, "A").End(xlUp).Row - 1)

is there a way of fixing this to avoid this problem, other than that works great, thanks again for your time.
 
Upvote 0
Hi

I was working on the assumption that the first sheet would always have names and this would be compared to subsequent sheets which may or may not have names. Can you be a bit more specific on what scenarios may occur so I can try to work around them.

Maybe PM me with an email address and we can work with a sample file that will cover the possibilities.


Tony
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
469
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
622
Micron
M
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
508
esben
E

Forum statistics

Threads
1,223,099
Messages
6,170,112
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top