Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unsure of my error?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unsure of my error?
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 "Derek Johansen" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unsure of my error?
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 "Derek Johansen" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unsure how to set up spreadsheet | Excel Discussion (Misc queries) | |||
Unsure what formula to use | Excel Discussion (Misc queries) | |||
Unsure of which forumla to use ? | Excel Discussion (Misc queries) | |||
Unsure of the Function, and How to Write It | Excel Worksheet Functions | |||
Unsure about which functions to use | Excel Worksheet Functions |