For Loop only proceeding till first iteration then closing - No error message

Endlesscroc

New Member
Joined
Jan 14, 2014
Messages
11
Hey, big day for me on the forums.. I have some code which is basically noting whether an invoice has been paid, if it has it makes up a receipt and then marks the spreadsheet which contains the payment information as "Yes" in the Receipt issued column.

The loop is initializing correctly and even performing what I want it to do but it is only proceeding until it finds the first case, changes that to Yes, creates the receipt and then it ends as though it has completed it's process correctly (ie no error message).

Any help would be greatly appreciated!





Code:
Sub RoundedRectangle1_Click()
    Worksheets("Invoices Raised").Activate
    Dim LastRow As Long
    Dim FormattedDate As String
    Dim FormattedPaidDate As String
    Dim Filledinvoice As Workbook
    Dim EmptyVATInvoice As Workbook
    Application.ScreenUpdating = False
    


'Determine Last Row
LastRow = WorksheetFunction.CountA(Range("M:M"))
    
    
     For Each m In Worksheets("Invoices Raised").Range("M1:M" & LastRow).Cells
        If m.Value = "No" Then
              If m.Offset(0, -10).Value <> "" And m.Offset(0, -10).Value <> "Unpaid" Then
                  Cells(20, 20) = LastRow
                  On Error Resume Next
                  m.Value = "Yes"
                  FormattedDate = Right(m.Offset(0, -11), 4) & "-" & Mid(m.Offset(0, -11), 4, 2) & "-" & Left(m.Offset(0, -11), 2)
                  FormattedPaidDate = Right(m.Offset(0, -10), 4) & "-" & Mid(m.Offset(0, -10), 4, 2) & "-" & Left(m.Offset(0, -10), 2)
                  
    
                  
                  Application.Workbooks.Open Filename:="J:\\Admin\Invoicing\Invoices\" & FormattedDate & " " & m.Offset(0, -12).Value & " " & m.Offset(0, -2).Value & ".xlsx"
                  Workbooks(FormattedDate & " " & m.Offset(0, -12).Value & " " & m.Offset(0, -2).Value).Activate
                  Set Filledinvoice = ActiveWorkbook
                  
                  
                  'Adjust cells to be in line with VAT invoice
                  Cells(9, 4).Value = "VAT reg no:"
                  Cells(9, 8).Value = "IE xxxxxxxxxx"
                    
                  If m.Offset(0, 1).Value = "" Then
                        Cells(10, 4).Value = ""
                        Cells(10, 7).Value = ""
                        Else:
                        Cells(10, 4).Value = "Our Ref"
                        Cells(10, 8).Value = m.Offset(0, 1).Value
                  End If
                    
                  Cells(15, 1).Value = "INVOICE"
                  Cells(30, 1).Value = ""
                  Cells(36, 1).Value = ""
                  Cells(38, 1).Value = ""
                  Cells(38, 1).Interior.ColorIndex = 2
                  Cells(39, 1).Value = ""
                  Cells(40, 1).Value = ""
                  Cells(41, 1).Value = ""
                  Cells(42, 1).Value = ""
                  Cells(43, 1).Value = ""
                  Cells(45, 1).Value = ""
                  Cells(45, 1).Interior.ColorIndex = 2
                  Cells(46, 1).Value = ""
                                   
                  On Error Resume Next
                  Application.Workbooks.Open Filename:="J:\\Admin\Invoicing\Development\Sample VAT Invoice.xlsx"
                  Set EmptyVATInvoice = Workbooks("Sample VAT Invoice")
                    
                  Dim i As Long
                  Dim j As Long
                  For i = 1 To 38
                  For j = 1 To 8
                  EmptyVATInvoice.Sheets("invoice").Cells(i, j).Value = Filledinvoice.Sheets("invoice").Cells(i, j)
                  Next j
                  Next i
                  EmptyVATInvoice.Sheets("invoice").Range("A1:G13").WrapText = False
                    
                    
                    
                    
                    
                   'Save populated invoice as "Date Name Invoice Number VAT invoice"
                   Application.DisplayAlerts = False
                   Workbooks(FormattedDate & " " & m.Offset(0, -12).Value & " " & m.Offset(0, -2).Value & ".xlsx").Close
                   
                   EmptyVATInvoice.Activate
                   ActiveWorkbook.SaveAs Filename:="J:\\Admin\Invoicing\VAT Invoices\" & FormattedPaidDate & " " & m.Offset(0, -12).Value & " " & m.Offset(0, -2).Value & " VAT Invoice.xlsx", FileFormat:= _
                   51, CreateBackup:=False
                   
                   Filledinvoice.Close
                   EmptyVATInvoice.Close
                   
                   
                  
                   
              End If
        End If
    Next m
Workbooks("2014 Invoice Database").Activate
                   
                   ActiveWorkbook.Save


End Sub
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Remove your On Error Resume Next statements, set break points inside each If statement and For/Next loop, then run it and ensure it is processing as expected.
 

Endlesscroc

New Member
Joined
Jan 14, 2014
Messages
11
Thanks for the tips.

Somehow I had forgotten that I had already fixed this bug... It's been a long morning..
 

Watch MrExcel Video

Forum statistics

Threads
1,123,153
Messages
5,600,022
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top