Printing Issue

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
628
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
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")
Change it to:
VBA Code:
Set Rng = ws.Range("A1:AA61,A62:AA122,A123:AA183")
 

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
628
Office Version
  1. 2019
Platform
  1. Windows
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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,278
Office Version
  1. 2010
Platform
  1. Windows
Which Next i
the first one or the second one ?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
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.
 
Solution

Forum statistics

Threads
1,148,256
Messages
5,745,698
Members
423,969
Latest member
CHHeights

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