Unsure of my error?

[Derek Johansen]

I am trying to find matches for certain cells in my spreadsheet, and when a
match based on the criteria is found, i want to Sum the values of another
column together. Here is what I have, but it doesn't work properly. This is
returning significantly different values depending on how the sheet is
sorted, when in theory, it should work regardless of how the sheet is sorted!

Sub Addupifmatch()

For k = 2 To 10000
qty = Cells(k, "h")

For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") = "1" Then
GoTo 1
ElseIf IsEmpty(Cells(i, "c")) Then
GoTo 1
ElseIf Cells(i, "c") = Cells(k, "c") Then
qty = qty + Cells(i, "h")
Rows(i).Delete
Else
End If
1 Next
Cells(k, "h") = qty
Next
End Sub

The first two parts of the if statement are because i want to ignore a few
types of data. ANy help would be much appreciated!

 

[JLGWhiz]

You do not need the first two parts of the If ...ElseIf statement. Unless
the criteria is met in the third part, it will ignore the other two
conditions anyhow. I think that adding the value property to your cell
references might solve the problem. Give it a try. If it does not work,
post back.

Sub Addupifmatch()

For k = 2 To 10000
qty = Cells(k, "h")

For i = Cells(Rows.Count, "c").End(xlUp).Row To 2 Step -1
If Cells(i, "c") .Value= Cells(k, "c") .ValueThen
qty = qty + Cells(i, "h").Value
Rows(i).Delete
End If
Next
Cells(k, "h") = qty
Next
End Sub

 

[Joel]

You must have some blank rows that is causing the problem. Try this code.

Sub Addupifmatch()

k = 2
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
Do
qty = Cells(k, "h")
For i = LastRow To k Step -1
If Cells(i, "c") <> "1" And _
Not IsEmpty(Cells(i, "c")) Then

If Cells(i, "c") = Cells(k, "c") Then
qty = qty + Cells(i, "h")
Rows(i).Delete
End If
End If
Next i
Cells(k, "h") = qty
k = k + 1
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
Loop While k <= LastRow
End Sub