Cost balance Macro in order to find missing items

[sheipineapple29]

Hi all,

I want to know if you can help me out. The data I have is below:

[TABLE="class: cms_table, width: 413"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]100020265[/TD]
[TD="align: right"]-7,671,860.08[/TD]
[TD][/TD]
[TD]7,671,860.08[/TD]
[TD]472023645[/TD]
[/TR]
[TR]
[TD]100020371[/TD]
[TD="align: right"]-6,302,466.47[/TD]
[TD][/TD]
[TD]7,671,860.08[/TD]
[TD]472023731[/TD]
[/TR]
[TR]
[TD]100020370[/TD]
[TD="align: right"]-2,972,991.07[/TD]
[TD][/TD]
[TD]1,838,071.62[/TD]
[TD]472021113[/TD]
[/TR]
[TR]
[TD]6200080166[/TD]
[TD="align: right"]-1,838,071.62[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like to find a macro that can do the next steps:

1. On the side of "A:B", on "C", SUM between "B" and "D"
- If "0", text "ok"
- If different to "0", add rows until the math SUM is "0"

2.On the side of "D:E", on "C" SUM between "B" and "D"
- If "0", text "ok"
- If different to "0", add rows until the math SUM is "0"

3. If not "0", show the total of the SUM.

Example:

[TABLE="class: cms_table, width: 413"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]100020265[/TD]
[TD="align: right"]-7,671,860.08[/TD]
[TD="align: center"]ok[/TD]
[TD]7,671,860.08[/TD]
[TD]472023645[/TD]
[/TR]
[TR]
[TD]100020371[/TD]
[TD="align: right"]-6,302,466.47[/TD]
[TD="align: right"]-6,302,466.47[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7,671,860.08[/TD]
[TD]7,671,860.08[/TD]
[TD]472023731[/TD]
[/TR]
[TR]
[TD]100020370[/TD]
[TD="align: right"]-2,972,991.07[/TD]
[TD="align: right"]-2,972,991.07[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6200080166[/TD]
[TD="align: right"]-1,838,071.62[/TD]
[TD="align: center"]ok[/TD]
[TD]1,838,071.62[/TD]
[TD]472021113[/TD]
[/TR]
</tbody>[/TABLE]



I have to do this with about 25000+ rows. The purpose is to find which ones do not have a match.

Hope you can understand what I am trying to request.

Thank you all.

Sheila

 

[onlyadrafter]

Hello,

this seems to work for the posted data. Don't know how long the code will take.

Sub SUMMING()
    Application.ScreenUpdating = False
    MY_ROWS = 1
    MY_LAST_ROW = Range("A" & Rows.Count).End(xlUp).Row
    Do Until MY_ROWS = MY_LAST_ROW - 1
        If Range("B" & MY_ROWS).Value + Range("D" & MY_ROWS).Value = 0 Then
            Range("C" & MY_ROWS).Value = "OK"
        Else
            If Not IsEmpty(Range("B" & MY_ROWS).Value) Then
                    If Not IsEmpty(Range("D" & MY_ROWS - 1).Value) Then
                        Range("D" & MY_ROWS & ":E" & MY_ROWS).Insert
                        MY_LAST_ROW = MY_LAST_ROW + 1
                    End If
                If Not IsEmpty(Range("D" & MY_ROWS).Value) Then
                    Range("A" & MY_ROWS & ":B" & MY_ROWS).Insert
                    MY_LAST_ROW = MY_LAST_ROW + 1
                End If
            End If
            Range("C" & MY_ROWS).Value = Range("B" & MY_ROWS).Value + Range("D" & MY_ROWS).Value
        End If
        MY_ROWS = MY_ROWS + 1
    Loop
    Application.ScreenUpdating = True
End Sub

Does this work for the rest of your data?