CODEWITHSUNDEEP
vbaexcel
user3509187
user3509187

Reputation: 11

Macro stops if statement false

The code below should print specific pages if the value in a cell is 1.

When the value of the cell being tested is not 1 the macro stops.

What can I change to make the macro continue to the end?

Sub PrintSalesDocs()

'
' PrintSalesDocs Macro
'

Sheets("Print Menu").Select

Range("C15").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Sales Doc"
Range("B5").Select

If Range("d19").Value = 1 Then
Sheets("trailers").PrintOut Copies:=1

If Range("d20").Value = 1 Then
Sheets("Pricing").PrintOut Copies:=1

If Range("d21").Value = 1 Then
Sheets("Q-Hours").PrintOut Copies:=1

If Range("d22").Value = 1 Then
Sheets("trailer customer info").PrintOut Copies:=1

If Range("d23").Value = 1 Then
Sheets("trailer job card").PrintOut Copies:=1

If Range("d24").Value = 1 Then
Sheets("running gear").PrintOut Copies:=1

If Range("d25").Value = 1 Then
Sheets("finishing").PrintOut Copies:=1

If Range("d26").Value = 1 Then
Sheets("boxes").PrintOut Copies:=1

If Range("d27").Value = 1 Then
Sheets("bottom dpr").PrintOut Copies:=1

If Range("d28").Value = 1 Then
Sheets("c-sider").PrintOut Copies:=1

If Range("d29").Value = 1 Then
Sheets("log trlr").PrintOut Copies:=1

If Range("d30").Value = 1 Then
Sheets("skel-fd").PrintOut Copies:=1

If Range("d31").Value = 1 Then
Sheets("tank trl").PrintOut Copies:=1

If Range("d32").Value = 1 Then
Sheets("stock tr").PrintOut Copies:=1

If Range("d33").Value = 1 Then
Sheets("panel").PrintOut Copies:=1

If Range("d34").Value = 1 Then
Sheets("transporter").PrintOut Copies:=1

If Range("d35").Value = 1 Then
Sheets("tipper-srb").PrintOut Copies:=1

If Range("d36").Value = 1 Then
Sheets("tipper-ars").PrintOut Copies:=1

If Range("d38").Value = 1 Then
Sheets("checksheet steer axle").PrintOut Copies:=1

If Range("d39").Value = 1 Then
Sheets("checksheet kingpin").PrintOut Copies:=1

If Range("d40").Value = 1 Then
Sheets("checksheet 5th wheel").PrintOut Copies:=1

If Range("d41").Value = 1 Then
Sheets("checksheet m911d").PrintOut Copies:=1

If Range("d42").Value = 1 Then
Sheets("checksheet finishing").PrintOut Copies:=1

If Range("d43").Value = 1 Then
Sheets("checksheet brakes").PrintOut Copies:=1

If Range("d44").Value = 1 Then
Sheets("checksheet pre-delivery").PrintOut Copies:=1

If Range("d45").Value = 1 Then
Sheets("checksheet quality").PrintOut Copies:=1

If Range("d46").Value = 1 Then
Sheets("checksheet pre-dispatch").PrintOut Copies:=1

If Range("d47").Value = 1 Then
Sheets("checksheet dispatch").PrintOut Copies:=1

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub

Upvotes: 1

Views: 574

Answers (2)

peter_the_oak
peter_the_oak

Reputation: 3710

I appreciate niton's much more readable code, and I see in your question a valueable opportunity to discuss refactoring. Watch this:

' Part 1: making a configuration
Dim table As Collection
Set table = New Collection

Dim row
row = Array("d19", "trailers")
table.Add row
row = Array("d20", "Pricing")
table.Add row
row = Array("d21", "Q-Hours")
table.Add row

...

row = Array("d46", "checksheet pre-dispatch")
table.Add row
row = Array("d47", "checksheet dispatch")
table.Add row

' Part 2: doing the work according to the configuration
For Each row In table
   If Range(row(0)) = 1 Then
      Sheets(row(1)).PrintOut Copies:=1
   End If
Next

This code has the following advantages:

  1. That way, the only code you have to give some thoughts are the last 5 lines. The rest is simple configuration that make the last lines work.
  2. For instance, with one next scenario step the whole configuration could be read e.g. from an XML file with nearly no effort. The last five lines would hardly change because of this.

I have roughly checked the code in Excel, so there should be no problem with it.

I hope this gives some inspiration :-)

Upvotes: 0

niton
niton

Reputation: 9179

Try this way instead.

Sub PrintSalesDocs()

'
' PrintSalesDocs Macro
'

Sheets("Print Menu").Select

Range("C15").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = "Sales Doc"
Range("B5").Select

If Range("d19").Value = 1 Then
    Sheets("trailers").PrintOut Copies:=1
End If

If Range("d20").Value = 1 Then
    Sheets("Pricing").PrintOut Copies:=1
End If
...

If Range("d46").Value = 1 Then
    Sheets("checksheet pre-dispatch").PrintOut Copies:=1
End If

If Range("d47").Value = 1 Then
    Sheets("checksheet dispatch").PrintOut Copies:=1
End If

End Sub

Upvotes: 2

Related Questions