Code Modify

A

ayazgreat

Well-known Member
Joined
Jan 19, 2008
Messages
1,151
How should i modify mentioned below codes to take result c2 too c25 in sheet3 by matching a2 to a25 ?

HTML: 
Sub Commission_Total()

Dim ThisCell As Range
Dim CommTotal As Long

For Each ThisCell In Sheet2.Range("A1:A" & Range("A65536").End(xlUp).Row)
If ThisCell.Value = Sheet3.Range("A2") And ThisCell.Offset(, 1) = "sc" Then
CommTotal = CommTotal + ThisCell.Offset(, 2).Value
End If
Next ThisCell

Sheet3.Range("C2") = CommTotal

End Sub
 
Sort by date Sort by votes
Sir Jindon

I have added a Date column in the following sheet and I am getting error message.

Sheet2 is containg the data

<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64></COLGROUP><TBODY></TBODY></TABLE>

<TABLE style="WIDTH: 394pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=525 border=0 x:str><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99cc00" width=77 height=17>Date</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>ID</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>St100</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>St300</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>St600</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>St1000</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>JP</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>HP</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39731">10-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1001</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1000" u1:num="1000">1,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="600">600</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="50">50</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="500">500</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="5">5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39731">10-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1025</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000" u1:num="2000">2,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="300">300</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1000" u1:num="1000">1,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="2">2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39731">10-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1089</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="4000" u1:num="4000">4,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="900">900</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:str="- " u1:num="0">- </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:str="- " u1:num="0">- </TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="500">500</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="1">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39731">10-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1236</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000" u1:num="2000">2,000</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1200" u1:num="1200">1,200</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="200">200</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="200">200</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000" u1:num="2000">2,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="10">10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39732">11-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1001</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="12000" u1:num="12000">12,000</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="9000" u1:num="9000">9,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="500">500</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="200">200</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1000" u1:num="1000">1,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="3">3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39732">11-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1025</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1200" u1:num="1200">1,200</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="300">300</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="50">50</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="500">500</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="1">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39732">11-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1089</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="800">800</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="600">600</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="25">25</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="750">750</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="9">9</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39733">12-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1236</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1600" u1:num="1600">1,600</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="300">300</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="50">50</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="850">850</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="4">4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39733">12-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1258</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="20000" u1:num="20000">20,000</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1800" u1:num="1800">1,800</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="600">600</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2500" u1:num="2500">2,500</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="25">25</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39733">12-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1892</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3000" u1:num="3000">3,000</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000" u1:num="2000">2,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="20">20</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="10">10</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="300">300</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="1">1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 58pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=77 height=17 x:num="39733">12-Oct-08</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num>1258</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000" u1:num="2000">2,000</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000" u1:num="2000">2,000</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="600">600</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="100">100</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="900">900</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num u1:num="20">20

</TD></TR></TBODY></TABLE>

Giving error message in this line

Code: 
  b(.item(a(i,1)), ii) = b(.item(a(i,1)), ii) + a(i, ii)

However I want the result same and not wanting to show date in result sheet.

And in Sheet3 I wish to have Result as mentioned below


<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=448 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #99cc00" width=64 height=17>
ID​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St100​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St300​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St600​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
St1000​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
JP​
</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #99cc00" width=64>
HP​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1001​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="13000">
13,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="9600">
9,600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="250">
250 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="8">
8 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1025​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3200">
3,200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="600">
600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="150">
150 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3">
3 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1089​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="4800">
4,800 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="100">
100 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="25">
25 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1250">
1,250 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="10">
10 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1236​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3600">
3,600 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1500">
1,500 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="250">
250 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2850">
2,850 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="14">
14 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1258​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="22000">
22,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3800">
3,800 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1200">
1,200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="200">
200 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3400">
3,400 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="45">
45 ​
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17 x:num>
1892​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT-COLOR: black; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="3000">
3,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2000">
2,000 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="20">
20 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="10">
10 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="300">
300 ​
</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-TOP-COLOR: black; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="1">
1 ​
</TD></TR></TBODY></TABLE>

Could you please help in said line code?
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
change
Code: 
a = Sheets("Sheet2").Range("a1").CurrentRegion.Value
to
Code: 
With Sheets("Sheet2").Range("a1").CurrentRegion
    a = .Resize(, .Columns.Count - 1).Offset(,1).Value
End With
 
Upvote 0
Great thanks Sir Jindon

Sir I have added a column in this data wich is having a criteria and I wish to have sum only those IDs which are equal to "L" in column C and equal to that Dates I give e.g 10-Oct-08 to 12-Oct-08 and result will display in same sheet.


Excel Workbook
ABCDEFGHI
1DateIDLogSt100St300St600St1000JPHP
210-Oct-081001L1,000600100505005
310-Oct-081025O2,0003001001001,0002
410-Oct-081089O4,000900--5001
510-Oct-081236L2,0001,2002002002,00010
611-Oct-081001L12,0009,0005002001,0003
711-Oct-081025O1,200300100505001
811-Oct-081089O800600100257509
912-Oct-081236L1,600300100508504
1012-Oct-081258L20,0001,8006001002,50025
1112-Oct-081892O3,0002,00020103001
1212-Oct-081258L2,0002,00060010090020
Sheet2


Thanks in Advance
 
Upvote 0
Yes Sir you are right it is indded a bad habit

I am really sorry for it , if you could please resolve it I would be very thankful to you for this kindness.
 
Upvote 0
I remember that this is not your first time to do like this and I have already warned it to you.

I have no intention to do things like this anymore, sorry.
 
Upvote 0
Sir Jindon

I tried myself to resolve it and somehow I have succeesed to resolve but thank you very for your help which enabled me to understand your code and got my desired result.

I say thanks to all MVPs in this forum and all the person who helped because I did not know abc of Vb before joining this forum, and I know that i will always have been learing from this forum.

Sir Jindon I am nothing but I tried, I do not know if I am right, i also know that there might be some mistakes But I will try my levels best to correct it.

Codes

Code: 
Sub Jtest()
Dim a, i As Long, b(), n As Long, z As String, sDate As Date, fDate As Date, Loc As String
Application.ScreenUpdating = False
a = Sheets("Distribution").Range("a1").CurrentRegion.Resize(, 8).Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    sDate = Sheets("Summary Report").Range("a2").Value
    fDate = Sheets("Summary Report").Range("b2").Value
    Loc = Sheets("Summary Report").Range("c2").Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 2 To UBound(a, 1)
           If (Not IsEmpty(a(i, 1))) * (IsDate(a(i, 1))) * (a(i, 1) >= sDate) * (a(i, 1) <= fDate) * (a(i, 3) = Loc) Then
            
                z = a(i, 2)
                If Not .exists(z) Then
                    n = n + 1: .Add z, n
                    b(n, 1) = a(i, 1): b(n, 2) = a(i, 2)
                End If
                b(.Item(z), 3) = b(.Item(z), 3) + a(i, 6)
                 b(.Item(z), 4) = b(.Item(z), 4) + a(i, 7)
                  b(.Item(z), 5) = b(.Item(z), 5) + a(i, 8)
            End If
        Next
    End With
    If n = 0 Then
        MsgBox "Nohting"
        Exit Sub
    End If
    With Sheets("Summary Report").Range("a6")
        .CurrentRegion.Clear
        With .Offset(1).Resize(n, 5)
            .Value = b
            .Borders.Weight = xlHairline
        End With
        With .Resize(, 5)
            .Value = [{"Date","ID","St1","St2","SC6"}]
            .BorderAround Weight:=xlThin
        End With
        .Resize(n + 1, 5).BorderAround Weight:=xlThin
    End With

I also tried to resolve this post with help of your post in two methed

http://www.mrexcel.com/forum/showthread.php?t=301583&page=2&highlight=ayazgreat

Ist Methoed
Code: 
Option Explicit
Private Sub Jtest1()
Dim a, i As Long, b(), n As Long, z As String, sDate As Date, fDate As Date, Loc As String
Dim LR As Long
Application.ScreenUpdating = False
a = Sheets("Distribution").Range("A6").CurrentRegion.Resize(, 18).Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    sDate = Sheets("Summary Report").Range("a2").Value
    fDate = Sheets("Summary Report").Range("b2").Value
    Loc = Sheets("Summary Report").Range("c2").Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 2 To UBound(a, 1)
           If (Not IsEmpty(a(i, 1))) * (IsDate(a(i, 1))) * (a(i, 1) >= sDate) * (a(i, 1) <= fDate) * (a(i, 4) = Loc) Then
            
                z = a(i, 2)
                If Not .exists(z) Then
                    n = n + 1: .Add z, n
                     b(n, 1) = a(i, 2):  b(n, 2) = a(i, 3)
                End If
                    b(.Item(z), 3) = b(.Item(z), 3) + a(i, 6)
                    b(.Item(z), 4) = b(.Item(z), 4) + a(i, 7)
                    b(.Item(z), 5) = b(.Item(z), 5) + a(i, 8)
                    b(.Item(z), 6) = b(.Item(z), 6) + a(i, 9)
                    b(.Item(z), 7) = b(.Item(z), 7) + a(i, 10)
                    b(.Item(z), 8) = b(.Item(z), 8) + a(i, 11)
                    b(.Item(z), 9) = b(.Item(z), 9) + a(i, 12)
                    b(.Item(z), 10) = b(.Item(z), 10) + a(i, 13)
                    b(.Item(z), 11) = b(.Item(z), 11) + a(i, 14)
                    b(.Item(z), 12) = b(.Item(z), 12) + a(i, 15)
                    b(.Item(z), 13) = b(.Item(z), 13) + a(i, 16)
                    b(.Item(z), 14) = b(.Item(z), 14) + a(i, 17)
                    b(.Item(z), 15) = b(.Item(z), 15) + a(i, 18)
            End If
        Next
    End With
    If n = 0 Then
        MsgBox "Nohting"
        Exit Sub
    End If
    
    With Sheets("TCS Summary Report").Range("a6")
        .CurrentRegion.Clear
        With .Offset(1).Resize(n, 14)
            .Value = b
            .Borders.Weight = xlHairline
            .Font.Name = "Book Antiqua"
            .Font.Color = 11219832
            End With
        
        With .Resize(, 14)
            .Value = [{"ID","Dealer Name","St1","St2","SC6","SC7","Jp","Jt Wt ", "Jk","PCO","VP","LS","PPS","PDS","HS"}]
            
            .BorderAround Weight:=xlThin
            .Font.Color = 14574768
            .Borders(xlEdgeBottom).LineStyle = xlDouble
            .Borders(xlEdgeBottom).Color = 14574768
            .HorizontalAlignment = xlCenterAcrossSelection
            .Font.Bold = True
            .Font.Name = "Book Antiqua"
            .VerticalAlignment = xlCenter
            .WrapText = True
        End With
        .Resize(n + 1, 14).BorderAround Weight:=xlThin
        End With
    
    With Sheets("Summary Report")
        .Range("A6:N6").End(xlDown).Sort Key1:=Range("A7"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1
        .Columns("H:I").Delete
        .Range("A7").Select
    End With
    
    With Sheets("Summary Report").Range("C6")
    .Resize(n + 1, 14).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
    End With
    With Sheets("Summary Report").Range("A7")
    .Resize(n + 1, 1).HorizontalAlignment = xlCenter
    End With
    LR = Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & LR + 1).Value = "Total:-"
        Range("A" & LR + 1, "L" & LR + 1).Font.Bold = True
        Range("A" & LR + 1, "L" & LR + 1).Font.Color = 11219832
        Range("A" & LR + 1, "L" & LR + 1).Borders(xlEdgeBottom).LineStyle = xlDouble
        Range("A" & LR + 1, "L" & LR + 1).Borders(xlEdgeBottom).Color = 11219832
        Range("C" & LR + 1).Value = WorksheetFunction.Sum(Range("C7:C" & LR))
        Range("D" & LR + 1).Value = WorksheetFunction.Sum(Range("D7:D" & LR))
        Range("E" & LR + 1).Value = WorksheetFunction.Sum(Range("E7:E" & LR))
        Range("F" & LR + 1).Value = WorksheetFunction.Sum(Range("F7:F" & LR))
        Range("G" & LR + 1).Value = WorksheetFunction.Sum(Range("G7:G" & LR))
        Range("H" & LR + 1).Value = WorksheetFunction.Sum(Range("H7:H" & LR))
        Range("I" & LR + 1).Value = WorksheetFunction.Sum(Range("I7:I" & LR))
        Range("J" & LR + 1).Value = WorksheetFunction.Sum(Range("J7:J" & LR))
        Range("K" & LR + 1).Value = WorksheetFunction.Sum(Range("K7:K" & LR))
        Range("L" & LR + 1).Value = WorksheetFunction.Sum(Range("L7:L" & LR))
        Range("C" & LR + 1, "L" & LR + 1).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
Application.ScreenUpdating = True
End Sub

2nd Method

Code: 
Sub Jtest2()
Dim a, i As Long, b(), n As Long, z As String, sDate As Date, fDate As Date, Loc As String
Dim LR As Long, ii As Long
Application.ScreenUpdating = False
a = Sheets("Distribution").Range("A6").CurrentRegion.Resize(, 18).Value
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
    sDate = Sheets("Summary Report").Range("a2").Value
    fDate = Sheets("Summary Report").Range("b2").Value
    Loc = Sheets("Summary Report").Range("c2").Value
   ' n = 1
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 2 To UBound(a, 1)
           If (Not IsEmpty(a(i, 1))) * (IsDate(a(i, 1))) * (a(i, 1) >= sDate) * (a(i, 1) <= fDate) * (a(i, 4) = Loc) Then
            
                z = a(i, 2)
                If Not .exists(z) Then
                    n = n + 1: .Add z, n
                     b(n, 1) = a(i, 2):  b(n, 2) = a(i, 3)
                End If
                 For ii = 6 To UBound(a, 2)
             b(.Item(z), ii - 3) = b(.Item(z), ii - 3) + a(i, ii)
            
        Next
        End If
    Next
    End With
    If n = 0 Then
        MsgBox "Nohting"
        Exit Sub
    End If
 
'Remaining same as above in first Method
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Macro that copies Excel data into pdf field doesn't always paste all the data
Replies
3
Views
631
RickOlson
R
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
213
mumps
mumps
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
73
VanGirl
V
S
  • Question
VBA: All Sheets data to Master Sheet (excluding some sheet)
2
Replies
11
Views
390
kevin9999
K
L
  • Question
VBA query - not opening outlook email but one drive
Replies
0
Views
172
Liverlee
L

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

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