Printing Issue

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
Office Version
  1. 2019
Platform
  1. Windows
I am trying to print ranges based on the last row number and the Listbox selection.

It gets down to this line then it says the compile error wrong number of arguments???

VBA Code:
Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183")

Full code:

VBA Code:
Private Sub Print_Job_Card_Click()

Dim i As Long, C As Long
Dim ws As Worksheet
Dim Rng As Range
Dim SheetArray() As String, SelectedItems As String
Dim x As Integer
Dim LRow As Long

Set ws = ThisWorkbook.Worksheets("Job Card Master")

    On Error Resume Next
    With Me.Print_Listbox
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    SelectedItems = SelectedItems & Me.Print_Listbox(i)
    ReDim Preserve SheetArray(C)
    SheetArray(C) = .List(i)
    C = C + 1
   
    If SelectedItems = "Job Card with Time Analysis" Then
   
    With ws

    LRow = .Cells(Rows.Count, 5).End(xlUp).Row
     
      If LRow = 66 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA66")
      End If
     
      If LRow = 125 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA125")
      End If
     
      If LRow = 183 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183")
      End If
     
      If LRow = 244 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183", "A184:AA244")
      End If
     
      If LRow = 307 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61", "A62:AA122", "A123:AA183", "A184:AA244", "A145:AA307")
      End If
      Rng.PrintOut
     
      End If
     
      End With
      End With

            Next i
            Sheets(SheetArray()).PrintOut

End Sub

Function PAForJobCard()
       With ws.PageSetup
      .LeftMargin = Application.InchesToPoints(0.2)
      .RightMargin = Application.InchesToPoints(0.2)
      .TopMargin = Application.InchesToPoints(0.2)
      .BottomMargin = Application.InchesToPoints(0.2)
      .HeaderMargin = Application.InchesToPoints(0.2)
      .FooterMargin = Application.InchesToPoints(0.2)
      .Orientation = xlLandscape
      .PaperSize = xlPaperA3
      End With
End Function
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Thanks, that's very good but why is Next i saying Next without For

VBA Code:
Private Sub Print_Job_Card_Click()


Dim i As Long, C As Long
Dim ws As Worksheet
Dim Rng As Range
Dim SheetArray() As String, SelectedItems As String
Dim x As Integer
Dim LRow As Long

Set ws = ThisWorkbook.Worksheets("Job Card Master")

    On Error Resume Next
    With Me.Print_Listbox
    For i = 0 To .ListCount - 1
    If .Selected(i) Then
    SelectedItems = SelectedItems & Me.Print_Listbox(i)
    ReDim Preserve SheetArray(C)
    SheetArray(C) = .List(i)
    C = C + 1
    Next i
   
    If SelectedItems = "Job Card with Time Analysis" Then
   
    With ws

    LRow = .Cells(Rows.Count, 5).End(xlUp).Row
     
      If LRow = 66 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA66")
      End If
     
      If LRow = 125 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA125")
      End If
     
      If LRow = 183 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA122,A123:AA183")
      End If
     
      If LRow = 244 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA122, A123:AA183,A184:AA244")
      End If
     
      If LRow = 307 Then
      Call PAForJobCard
      Set Rng = ws.Range("A1:AA61,A62:AA122,A123:AA183,A184:AA244,A145:AA307")
      End If
      Rng.PrintOut
          Next i

     
      End With
     

           
            Sheets(SheetArray()).PrintOut

End Sub
 
Upvote 0
Which Next i
the first one or the second one ?

You only have one For i = loop and If .Selected(i) Then doesn't have a matching End If before the first Next i

I'm not sure of the intended logic, but you have mismatched For ... Next, With...End With and If ... End If blocks. Use proper indentation for every such block and you should see whether the code structure is correct or not.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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
Back
Top