Posts by markc

    Hi all,

    It's been several years sine I had to look at calculating amount of time worked.
    Can you please look at this old spreadsheet of mine and verify that the formula is correct?

    It appears to be ok to me, but I don't want any errors when it comes to paying my employees



    Re: Print Named Ranges

    Hi Dave,

    Thanks for that - very useful and I get a better understanding of working with printing named ranges

    I'm still stuck on the idea of reading the values from a worksheet however.
    I envision a table that the user can setup and modify as needed for all page setup settings as Roy posted here for a multi-line header read from a worksheet.

    I'm thinking that I can read the list of named ranges into a combo box, then based on my or other user's selection of a single page to print from the workbook all page setup settings would update based on the index value of the combo box and the corresponding values for page setting stored to the right of each named range

    A B C D
    ===== ========= ======== =======
    named Header Header Header
    range Line 1 Line 2 Line 3
    ====== ========== ======== =======
    tblWeights Looney Tunes, Inc. Bugs Bunny Sub., Inc Consolidated Rollup

    This table should continue out to exhaust all possible page setup options
    Appears to be 25 other page setup options that can be stored on a worksheet and values read when a user chooses an item from a combo box. This would make a very nice customizable print manager to be sure

    A small workbook with the table setup is attached

    Thanks again for your help

    Re: Print Named Ranges

    Hi PCI,

    Many thanks for your help.
    It is a generic vba solution that I am trying to create so that I can port a homemade printing manager to many different workbooks. I find the downloadable Report Manager very lacking.

    At any rate, I downloaded the file and took a look
    Appears to only get the names of named ranges in the workbook and list them.

    My code already does that (many more lines than yours). But it works.

    Now what I would like to do in the next procedure is read the name of the range that was listed
    And then print that range. In the end, I will loop through all of the named ranges and print each one.

    Thanks again for your help.
    marc[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Hi all -

    I've done quite a bit of searching on the internet and tried many things - all of which fail.

    The code below works up to

    Debug.Print strName

    Which returns the string tblWeightDist as expected (tblWeightDist is a named range - name is stored in a cell in the workbook)

    The code below error's at

    Set rngPrint.Name = strName

    So apparently Excel does not like me trying to assign a string variable to a

    I guess where I am struggling is

    • how to now pass strName into a named range
    • get the address of the named range
    • printout the address

    Full code below

    Thanks much for the great help

    Re: Print Named Ranges

    Hi Dave

    Thanks for your help.
    unfortunately, I'm not sure where you are going.

    I inserted a new sheet and pasted the name into cell A1
    It returned a #Value! error

    I see that I used this snippet in some other code:

    With Sheets("Market") 
            .PageSetup.PrintArea = rngPrint.Address 
        End With

    A little like what I am trying to do now, except the activesheet at the moment is named "prnt" The named range exists on some other sheet in the workbook. Only the text name is stored on the sheet "prnt"

    Once I have read the name of the range to be printed, is it possible to make the sheet with the named range active and then assign the named range to the print range?

    Thanks much

    Hi all -

    I am working on building a better report manager using named ranges

    1.) Loop through the workbook and find all named ranges
    Output the named ranges to a worksheet

    2.) Read the values from the worksheet and print each one

    Seem simple enough and is really just first step
    Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed

    But I'm getting ahead of myself.
    First is to print a named range

    My code is below
    Error msg:


    Run Time Error 91
    Object variable or With Block Variable Not Set

    Debug points here:

    Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value

    Though I'm not really sure as to why

    Thanks much

    Full code:

    1. Loop through all named ranges and output to worksheet. This part works fine:

    Snippet below is where I am trying to print the named range

    Re: Charts Data Point Appears Cut-off Along Y-Axis

    Thanks Dave -

    Will do on uploading small samples instead of screen shots.
    Both charts are incorrect at the moment

    The first cuts-off part of the the first data point, the second moves the data point slightly to the right of the origin.

    I would like the datapoint to appear as all other points and begin at the origin (0,0)

    Best regards,

    Hi all -

    The first data point of my chart appears cutoff along the y axis
    I formatted the x-axis by clearing the check box "Value (Y) axis crosses at maximum category"
    If I add the tick mark back in then my first data point does not begin at the origin.

    Any idea what I am doing wrong?
    Screen Shot attached of both cases

    Best regards,

    Re: Linked Range Image Altered On Paste

    Hi Dave -
    The row below Participation 2007 is formatted for height of 3
    As is the row below Participation 2006

    However, the linked range appears as though the row below Participation 2006 is thicker.
    Though they are definitely formatted exactly the same.

    Instead of using the camera tool, I copied the range to the clipboard, switched to the final report, held the shift key, and clicked on Edit..Paste Picture Link
    The image now appears as it should

    Thanks much
    Best regards,

    Hi all -

    I have a range all setup and I take a picture of it with the camera tool
    But when I paste the object on the final report, the image is off slightly
    Namely 2 rows are formatted for the same height (3) and left with no inputs, no formatting
    But when I paste the object one row appears thicker than the other

    Why might that be and how do I fix it?
    The image is attached

    Best regards,

    Hi all

    I would like to highlight cells is two conditions are met:

    • Cell = 0
    • Offset(0,-1)>0

    I tried the conditional format wizard and entered a formula:
    But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard

    Best regards,

    Re: Toggle Total Formula Between Year To Date And Total Year

    Thanks AAE -

    Looks like your formula defines a period as a quarter
    However, there are 12 periods in a year, not truly a month (4-4-5 calendar)
    Also your formula does not allow for YTD status if in-between the quarters.

    What if through period 5 (February)
    The total should include Period 1 (October) thru Period 5 (February)

    I don't think Nested IF statments will work
    Even if they would, that would be one brutal formula.

    Best regards,

    Re: Toggle Total Formula Between Year To Date And Total Year

    Thanks guys!

    I think you are on the right track
    The formula doesn't seem to return the expected result

    For example, enter 2 in cell A1
    The desired result in F18 is 146.2

    It appears you are working upwards from the total rather downwards from the start of the Fiscal Year. The notation in Col E for the periods with the start at October

    Is it an easy fix to alter the formula to start at F5 and work downward based on the selection in $A$1?

    Thanks again.
    Best regards,

    Hi all -

    I have a sheet in my workbook with at least 180 small tables, there may be more.
    I woulds like to be able to change total formulas for all tables at once to show either year-to-date or total year.

    For example:
    If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.

    Attached small sample of one of the tables
    Thanks much.

    Best regards,

    Re: Swap Series In Stacked Area Chart

    Thanks Andy -

    I'll give it a go and come back on the column charts
    My instinct is that it will appear busy

    I also like that the trend and ytd avg are different chart types and makes each visually appealing in their own right, even though they appear on the same chart

    Thanks much