0

Our current system for statement is very manual. It is in an Excel workbook that pulls data from the accounting system. To automate this I have created the following macros:

  • TableRefresh.Refresh - This refreshes the Pivot Table (Table 1) on sheets("Aged Balances")

  • Lines.StatementLines - This pulls in the necessary lines for the statement

  • PDFEmail.EmailPDF - if Range("I6") is Email then this attaches the statement to an email as a PDF and sends it

  • StatementPrint.PrintStatement - if Range("I6") is Email then this prints the statement ready for posting

  • DB2Clear.ClearDB2 - Clears the statement lines ready for the next customers info

Now I am trying to create a macro that pulls those all together. Table 1 (mentioned above) has all the customers, and their Client Codes (Column 1), for which statements need to be created. This is what I have currently:

Sub Statements()
    Dim i As Integer
    Dim LastRow As Long

    With Sheets("Aged Balances")
        LastRow = .Cells(.Rows.Count, 14).End(xlUp).Row
    End With

    Call TableRefresh.Refresh

    'Now the loop begins.
    For i = 3 To LastRow

    'Now set the new Client Code
    Worksheets("Statement").Range("K3").Value = Worksheets("Aged 
    Balances").Cells(i, 1).Value

    Call Lines.StatementLines

        If Sheets("Statements").Range("I6").Value = "Email" Then
            Call PDFEmail.EmailPDF
        ElseIf Sheets("Statements").Range("I6").Value = "Print" Then
            Call StatementPrint.PrintStatement
        Else
            Return
        End If

        MsgBox "Statement Complete", vbInformation
        Call DB2Clear.ClearDB2
    Next i

    MsgBox "Statements Complete", vbInformation    
End Sub

When using the F8 step through method I noticed that it gets to the first line of the loop section(For i = 3 To LastRow) then completely skips the whole loop section and goes to the Message Box.

Thanks in advance for your help and advice on this. Most appreciated.

Community
  • 1
  • 1
Beth
  • 23
  • 5
  • First you got `For i = 3 To LastRow` twice in your code (which might be a typo). Second this is a very broad question (you even didn't ask one). You need to ask a *specific* question to get an answer here. Questions like "*I want to do A and then B. Can someone help me?*" are considered to be off-topic on Stack Overflow. You can read [Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) and also [ask] and ask a specific question. – Pᴇʜ May 04 '17 at 06:55
  • @Peh - Thank you for your comments. Most helpful. Yes the double up of code was just entry error. Also thanks for the link to the Debugging/Trouble shooting article. Now that I know more accurately what is actually happening, I have edited my post(see comments after the code). I now know the issue but not how to solve it. I am a beginner VBA'er and haven't met this issue before. – Beth May 04 '17 at 21:04
  • I suspect the value of `lastRow` is less than 3, thus the loop won't initialize. See the brilliant answers [here](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) for more reliable ways of obtaining the "last" row of data in a given range or worksheet. – David Zemens May 04 '17 at 21:10
  • Thanks @DavidZemens. Great resource. I found some code that worked although based on that post it is not the most reliable. However for what I want to do it is enough :) `With Sheets("Aged Balances") lastRow = .Range("A" & .Rows.Count).End(xlUp).Row End With` This is what I ended up using. – Beth May 05 '17 at 02:26

1 Answers1

0

Here is the final code:

Sub Statements()
Dim i As Integer
Dim lastRow As Long

'This was where I was having issues. Because the table sometimes had less than 3 rows it was skipping the loop.
        With Sheets("Aged Balances")
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With

        Call TableRefresh.Refresh

        'Now the loop begins.
        For i = 3 To lastRow

        Worksheets("Statement").Range("K3").Value = Worksheets("Aged Balances").Cells(i, 1).Value

'Because I changed the code above it no longer takes into account that it is a Pivot Table so I have put in this If statement so that it doesn't include the last two lines of the table(blank and Grand Total).
        If Sheets("Statement").Range("I6").Value = "" Then
        Exit For

        ElseIf Worksheets("Statement").Range("I6").Value = "Email" Then
        Call Lines.StatementLines
        Call PDFEmail.EmailPDF

        ElseIf Sheets("Statement").Range("I6").Value = "Post" Then
        Call Lines.StatementLines
        Call StatementPrint.PrintStatement

        End If

        Call DB2Clear.ClearDB2

Next i

     MsgBox "Statements Complete", vbInformation

End Sub

Thanks to @Peh and @DavidZemens for their comments that helped me get this. It seems that they were correct in that it skipped the loop whenever To LastRow was less than 3. See comments in the code.

Now I've gained more time to work on other automation's!

Beth
  • 23
  • 5