Results 1 to 9 of 9
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Print Copies by Range (Excel 2000)

    I need help with a routine whereby the number of non blank entries in a column range (C11:C35) is counted, and the result from the count replacing Copies: for printing.
    I am a learner (very) to VBA.
    <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print Copies by Range (Excel 2000)

    As all my code is, this is crude and will need tuning, but should get you started:

    Option Explicit
    Sub PrintOutThisMany()
    Dim dblCount As Double
    On Error Resume Next ' in case none are found
    'next line counts all constants and all formulas in the range, your intent may vary
    dblCount = Worksheets("Sheet1").Range("C11:C35").SpecialCells (xlCellTypeConstants, 23).Count + _
    Worksheets("Sheet1").Range("C11:C35").SpecialCells (xlCellTypeFormulas, 23).Count
    Worksheets("Sheet1").PrintOut Copies:=dblCount
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Print Copies by Range (Excel 2000)

    Thanks John. This is what I amended your code to, however I must have created a problem. When I click the button nothing prints. I put this code into a new module due to the Option Explicit.
    Option Explicit
    Sub AucklandUphlFullPerfSheets()
    '
    ' AucklandUphlFulPerfSheets Macro

    Dim dblCount As Double
    On Error Resume Next
    dblCount = Worksheets("Upholstery").Range("C14:C33").SpecialC ells(xlCellTypeConstants, 20).Count + Worksheets("Upholstery").Range("C14:C33").SpecialC ells(xlCellTypeFormulas, 20).Count
    With Worksheets("Upholstery").Select
    Sheets("Upholstery").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=dblCount
    End With
    End Sub
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print Copies by Range (Excel 2000)

    Paul, the numeric second argument to SpecialCells(arg1, arg2) has to be a number which is the the of sum one or more of the following, according to what you intend to select:

    Numeric/Value = 1
    Text/String =2
    Logical/Boolean (True/False) = 4
    Error (#ERR, #NA, #DIV/0!, etc.) = 16

    Since you put 20 where I had 23, you are telling SpecialCells to look for Logical and Error Values (4 + 16 = 20); so my guess is that there are -zero- Logicals and Errors in Worksheets("Upholstery").Range("C14:C33"), so dblCount is zero, so zero pages get printed. If you want to look only for numeric values, use 1 as in

    ...SpecialCells(xlType..., 1)
    ... for text only values ...SpecialCells(xlType...., 2)
    ... for numeric and text only values ...SpecialCells(xlType..., 3) '(sum of 1 + 2)
    ... for numeric and error only values ...SpecialCells(xlType..., 17) '(sum of 1 + 16)
    etc.

    Also, FWIW the activate and select parts of your code are redundant and if you want that Ws displayed, only

    Worksheets("Upholstery").Activate

    is necessary. HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Print Copies by Range (Excel 2000)

    Oh dear, <img src=/S/blush.gif border=0 alt=blush width=15 height=15>That will teach me to assume, I thought it was the number of rows to test <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    Made the changes but still have a problem. Attached is an edited version of the file which may provide better clues than I can. Thank you
    Attached Files Attached Files
    Paul Coyle
    Approach love and cooking with reckless abandon

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print Copies by Range (Excel 2000)

    Paul, the problem was bad code on my part; in this code, if there were either no constants or there were no formulas in the range, the line would be ignored as a result of the On Error instruction, so dblCount never got set to anything, so it kept it's default value of zero.

    On Error Resume Next ' in case none are found
    dblCount = Worksheets("Sheet1").Range("C11:C35").SpecialCells (xlCellTypeConstants, 23).Count + _
    Worksheets("Sheet1").Range("C11:C35").SpecialCells (xlCellTypeFormulas, 23).Count

    That code should have been:

    On Error Resume Next ' in case no constants are found
    dblCount = Worksheets("Sheet1").Range("C11:C35").SpecialCells (xlCellTypeConstants, 23).Count
    On Error Resume Next ' in case no formulas are found
    dblCount = dblCount + Worksheets("Sheet1").Range("C11:C35").SpecialCells (xlCellTypeFormulas, 23).Count

    However, now that I've seen your sample, assuming that the button is always on the sheet being printed and the values in the range will always be entered numeric values, your code requirement can be boiled down to:

    Sub AucklandUphlFullPerfSheets()
    With Worksheets("Upholstery")
    On Error Resume Next
    .PrintOut Copies:=.Range("C14:C33").SpecialCells(xlCellTypeC onstants, 1).Count
    End With
    End Sub

    You may also want to remove the unused modules, unless they contain code in the complete working version. I get an error message when I open the WB up, but can't see the reason.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Print Copies by Range (Excel 2000)

    Great, thank you! I was also coming to the conclusion that the count was zero, but do not know enough about VBA to check.
    Your solution works exactly as required, <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Print Copies by Range (Excel 2000)

    If you are learning VBA, there's a couple of simple things you can stick in the code that will help you see what's going on (for folks like me for whom VBA is a diversion but not a vocation). At the appropriate point in the code insert:

    MsgBox Typename(<some_variable_or_expression_or_object>) ' this tells you what kind of "thing" you are dealing with when you get type mismatch

    MsgBox <somevariable_or_expression> ' will tell you the value or text content, but it may also blow up, see line above
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Print Copies by Range (Excel 2000)

    Wow, thank you for going the extra mile and adding info that helps me become more skilled. Very much appreciated.
    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> <img src=/S/flags/NewZealand.gif border=0 alt=NewZealand width=30 height=18>
    Paul Coyle
    Approach love and cooking with reckless abandon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •