Solved

# Excel print range

Posted on 2012-04-13
237 Views
I have a macro that I use to print a range of cells.  If it does not = 0 then it prints.  It worked until I tried the same macro in the Excel 2010 - it works except that it prints all - even if the sum is zero.

Any help is appreciated.

For x = 6 To 19
If Sheet2.Cells(x, 8).Value <> 0 Then
y = x - 3
Sheets(y).Select
ActiveSheet.PageSetup.PrintArea = "\$B\$2:\$F\$21"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next x

For x = 6 To 19
If Sheet2.Cells(x, 11).Value <> 0 Then
y = x - 3
Sheets(y).Select
ActiveSheet.PageSetup.PrintArea = "\$I\$2:\$M\$21"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next x
For x = 6 To 19
If Sheet2.Cells(x, 14).Value <> 0 Then
y = x - 3
Sheets(y).Select
ActiveSheet.PageSetup.PrintArea = "\$b\$22:\$f\$41"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next x
For x = 26 To 39
If Sheet2.Cells(x, 14).Value <> 0 Then
y = x - 23
Sheets(y).Select
ActiveSheet.PageSetup.PrintArea = "\$I\$22:\$M\$41"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
Next x
End Sub
0
Question by:JohnMac328
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 6

Expert Comment

ID: 37844577
Have you tried inserting a breakpoint at the beginning of your code, then step through the code to check the values line by line?
0

Author Comment

ID: 37844583
No - trying to help someone out - not an excel person :)
0

LVL 2

Expert Comment

ID: 37845100
Maybe the values are not really 0 and need to be rounded:
Example:
``````Round(Sheet2.Cells(x, 14).Value, 2)
``````

Adjust ',2' up to the number of digits needed.
0

LVL 42

Accepted Solution

dlmille earned 500 total points
ID: 37845472
FYI - you can optimize your code from:

``````
If Sheet2.Cells(x, 8).Value <> 0 Then
y = x - 3
Sheets(y).Select
ActiveSheet.PageSetup.PrintArea = "\$B\$2:\$F\$21"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End If
``````

To

``````    If Sheet2.Cells(x, 8).Value <> 0 Then
y = x - 3
With Sheets(y)
.PageSetup.PrintArea = "\$B\$2:\$F\$21"
.PrintOut copies:=1
End With

End If
``````

There's no need to select sheets or use the ActiveWindow to set print areas or print sheets.

I just tested your code out with dummy data and it worked fine for me.  I put a 1 in H6,  K6, N6, and N26 and it printed 4 times.

The test is for non-zero so rounding should not be an issue.  Just check those ranges and advise if the value is not zero.  If that doesn't work, then try this and see what it tells you:

You can try this amended code below which creates a LogSheet (assuming the workbook is not protected) and logs what is happening.  Check the log and see what values are being displayed for these ranges.  If you are getting nothing, then the ranges being examined don't have data - or at least they don't have non-zero data, and that's what the if statement is looking for.

``````Sub test()
Dim wkb As Workbook
Dim wksNew As Worksheet
Dim outCursor As Range

Set wkb = ThisWorkbook

On Error Resume Next
Set wksNew = wkb.Worksheets("Log Sheet")
If Err.Number <> 0 Then
wksNew.Name = "Log Sheet"
End If
On Error GoTo 0

wksNew.Cells.Clear
Set outCursor = wksNew.Range("A1")

For x = 6 To 19
outCursor.Value = "BLOCK 1 Sheet2.Cells(" & x & ",8).Address is  = " & Sheet2.Cells(x, 8).Address & " having value: " & Sheet2.Cells(x, 8).Value
Set outCursor = outCursor.Offset(1, 0)
If Sheet2.Cells(x, 8).Value <> 0 Then
y = x - 3
With Sheets(y)
.PageSetup.PrintArea = "\$B\$2:\$F\$21"
.PrintOut Copies:=1
End With
End If
Next x

For x = 6 To 19
outCursor.Value = "BLOCK 2 Sheet2.Cells(" & x & ",11).Address is  = " & Sheet2.Cells(x, 11).Address & " having value: " & Sheet2.Cells(x, 11).Value
Set outCursor = outCursor.Offset(1, 0)
If Sheet2.Cells(x, 11).Value <> 0 Then
y = x - 3
With Sheets(y)
.PageSetup.PrintArea = "\$I\$2:\$M\$21"
.PrintOut Copies:=1
End With
End If
Next x
For x = 6 To 19
outCursor.Value = "BLOCK 3 Sheet2.Cells(" & x & ",14).Address is  = " & Sheet2.Cells(x, 14).Address & " having value: " & Sheet2.Cells(x, 14).Value
Set outCursor = outCursor.Offset(1, 0)
If Sheet2.Cells(x, 14).Value <> 0 Then
y = x - 3
With Sheets(y)
.PageSetup.PrintArea = "\$b\$22:\$f\$41"
.PrintOut Copies:=1
End With
End If
Next x
For x = 26 To 39
outCursor.Value = "BLOCK 4 Sheet2.Cells(" & x & ",14).Address is  = " & Sheet2.Cells(x, 14).Address & " having value: " & Sheet2.Cells(x, 14).Value
Set outCursor = outCursor.Offset(1, 0)
If Sheet2.Cells(x, 14).Value <> 0 Then
y = x - 23
With Sheets(y)
.PageSetup.PrintArea = "\$I\$22:\$M\$41"
.PrintOut Copies:=1
End With
End If
Next x

End Sub
``````

Cheers,

Dave
0

LVL 2

Expert Comment

ID: 37845832
"The test is for 0 so rounding should not b an issue"
-> I did actually run in this kind of issue in the past where the sum of values looked as if it was zero, but they were not.  In the end these are floating point numbers and rounding will make a near zero a perfect zero.
0

LVL 42

Expert Comment

ID: 37845856
I withdraw my comment, you are absolutely correct. 00000000001 may be zero to excel.

Let's see what the test I submitted provides and hopefully there's some answer there as well.

Cheers,

Dave
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month6 days, 4 hours left to enroll