Adding contents of cells for duplicates

[excel_noob86]

Hi,

I have an employee sheet which has multiple hours rows for one employee, i want to select an employee ID and add corresponding hours and place both in a separate sheet. Here's my code so far.

Sub dup()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set NewSheet = Worksheets.Add
NewSheet.Name = "NEW"
Set NewSheet = Worksheets.Add
supcount = 2
     countresources = 0
     Do While Sheets("RS Nov 2009").Range("A" & supcount).Value <> ""
        countresources = countresources + 1
        supcount = supcount + 1
Loop
'Dim temp As Range
Dim i, j, hours As Integer
hours = 0
Sheets("RS Nov 2009").Select
     Columns("A:A").Select
     Selection.Copy
     Columns("Q:Q").Select
     Selection.Copy
For i = 0 To supcount
For j = i + 1 To supcount
If Sheets("RS Nov 2009").Range("A" & i).Value = Sheets("RS Nov 2009").Range("A" & j).Value Then
hours = hours + Sheets("RS Nov 2009").Range("Q" & j).Value
Sheets("NEW").Range("A" & i).Value = Sheets("RS Nov 2009").Range("A" & i).Value
Sheets("NEW").Range("B" & i).Value = hours
End If
Next
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Iam getting an error 1004 at

If Sheets("RS Nov 2009").Range("A" & i).Value = Sheets("RS Nov 2009").Range("A" & j).Value Then

Can anybody help me please?? Thanks in advance

 

[ProSolutions]

Try using this instead

Range(Range("A" & ActiveCell.Row), Range("IV" & ActiveCell.Row).End(xlToLeft)).Select</PRE>

 

[excel_noob86]

Iam sorry but a beginner to macro... I dint quite get what iam supposed to do with that...

 

[ProSolutions]

If Sheets("RS Nov 2009").Range(Range("A" & ActiveCell.Row), Range("IV" & ActiveCell.Row).End(xlToLeft)).Select

So sorry, try this code instead of the line you had where it was erroring out.

 

[excel_noob86]

I tried dat. D code runs but it doesn't do what i want at all. Cn anybdy help??

 

[excel_noob86]

If Sheets("RS Nov 2009").Range(Range("A" & ActiveCell.Row), Range("IV" & ActiveCell.Row).End(xlToLeft)).Select

So sorry, try this code instead of the line you had where it was erroring out.

I get the same error od this one...

 

[ProSolutions]

Can you post the sheet itself so I can see what you have there?

 

[excel_noob86]

Can you post the sheet itself so I can see what you have there?

I dint know how else to do dis. M pasting part of my sheet. Basically i just want to copy this into another sheet but that sheet cant hve duplicates so i need to add the hours...
<TABLE style="WIDTH: 157pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=210 border=0 x:str><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black 1pt solid; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #3366ff" width=79 height=18>ID</TD><TD class=xl25 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: black 1pt solid; BORDER-LEFT: black; WIDTH: 98pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #3366ff" width=131>Hours</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver 1pt solid; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>1173901</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver 1pt solid; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="20">20</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>1173901</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="140">140</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0069968</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="-1.7763568394002505E-15">-1.77636E-15</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0884552</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="164">164</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0885001</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C08856</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="120">120</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910023</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910396</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="23.9998">23.9998</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910642</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="168">168</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0910698</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="197.0001">197.0001</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911145</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="95">95</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911379</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="200">200</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911474</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="128">128</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911710</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="220">220</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911749</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911993</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="40">40</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0911993</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="120">120</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912190</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="183">183</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912239</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="128">128</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912516</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912598</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="136">136</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912603</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="171">171</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0912726</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0913061</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0913214</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="112">112</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0913230</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="160">160</TD></TR><TR style="HEIGHT: 4.5pt; mso-height-source: userset" height=6><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 4.5pt; BACKGROUND-COLOR: white" height=6>C0915720</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="75">75</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0915874</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="161.5">161.5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0915877</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="120">120</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 1pt solid; BORDER-BOTTOM: silver 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18>C0915905</TD><TD class=xl26 style="BORDER-RIGHT: silver 1pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; BORDER-BOTTOM: silver 1pt solid; BACKGROUND-COLOR: white" align=right x:num="180">180</TD></TR></TBODY></TABLE>

 

[ProSolutions]

supcount = 2 (what is this and where did you dimension it?)

hours = hours + Sheets("RS Nov 2009").Range("Q" & j).Value (what is hours = hours + sheets.etc.....?)

 

[ProSolutions]

One more thing would it be easier to add a UserForm in which you could have a ListBox control for the ID and if you select ID #111111 it would check for duplicates and if there are dups then add the two dups hours together and paste the ID and the new value for the hours in a new sheet? Is this what you are looking to do?