Why Does This VBA Code Cause Duplicate Entries

Question

This is my first query on this site and I'm only an occasional user of VBA. The code below may not be very elegant, but it has worked.

I have an Excel Workbook which is of my own design and is essentially an accounting tool. The workbook has a 'Journal' sheet to which I post every transaction. On running the VBA code, each transaction is posted to a specified account (or worksheet). One sub routine posts what I would call General Transactions, while another posts what I call Inter Account Transfers, i.e. transferring a transaction that started off in one account to another - so there is a debit to the first account and a credit to the second account. The Inter Account Transfer routine runs after the General Transaction routine, so posts will always appear on the row immediately after all General Transactions. I also have a routine that clears the contents of all Account worksheets before the rest of the routines run.

The problem I am having is with 'debit' Inter Account Transfers. When I run the full code the first time, everything is ok and the Inter Account Transfer debit appears where it should - below the the last General Transaction. If I run it again, despite that run clearing all contents on the Account sheet, the Inter Account Transfer appears twice and if I run it again it appears three times, etc. etc. This only happens though, if there are more transactions on the debit side of the account. Also it does not happen at all on the Credit side.

The Account sheets have six columns - A to F, defined as follws: A = 'Date (Cr)', B = 'Description/Sub Category (Cr)', C = 'Amount (Cr)', D = 'Date (Dr)', E = 'Description/Sub Category (Dr)', F= 'Amount (Dr)'.

The code I use is shown below - rather than show it all, I've just shown the routine for one item from each of the Credit and Debit sides.

'DESCRIPTION/SUB-CATEGORY (Cr)
With Sheets(strWSJournal)
    I = 1
    LASTROW = .Range("A" & Rows.Count).End(xlUp).Row
    Set MyRG = .Range("B2:B" & LASTROW)
        For Each F In MyRG
            strWSGoToSheet = F.Offset(0, 1)

             With Sheets(strWSGoToSheet)
                LASTROW2 = .Range("B" & Rows.Count).End(xlUp).Row
            End With


                If (F.Value = strInterAcTran) Then
                    Sheets(strWSGoToSheet).Cells(LASTROW2 + I, "B") = F.Offset(0, 2)
                End If
        Next F
End With



'DESCRIPTION/SUB-CATEGORY (Dr)
With Sheets(strWSJournal)
    I = 1
    LASTROW = .Range("K" & Rows.Count).End(xlUp).Row
    Set MyRG = .Range("L2:L" & LASTROW)
        For Each F In MyRG
            strWSGoToSheet = F.Offset(0, 1)

            With Sheets(strWSGoToSheet)
                LASTROW2 = .Range("E" & Rows.Count).End(xlUp).Row
            End With


                If (F.Value = strInterAcTran) Then
                    Sheets(strWSGoToSheet).Cells(LASTROW2 + I, "E") = F.Offset(0, 2)
                    'I = I + 1
                End If
        Next F
End With

As can be seen, the code is identical apart from the cell references, so I can't understand what's going wrong. If any more info is required to help me resolve this please let me know.

Patrick Lepelletier Patrick Lepelletier · Accepted Answer · 2014-02-27T10:46:33

you forgot to specify the sheet in wich you are counting rows at each .Range(...& rows.count)

change it to .range(... & .rows.count)

basically, add a point before each rows.count.

Why Does This VBA Code Cause Duplicate Entries

1 Answers