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

Formula:
=ROUND(HOUR($J3-$G3)+(0.01*MINUTE($J3-$G3)*1.67),1)-ROUND(HOUR($I3-$H3)+(0.01*MINUTE($I3-$H3)*1.67),1)

Thanks
Regards,
marc

Re: Add Shadow To 3D Chart

Thanks Andy

Regards,
marc

Hi all,

Is it possible to add a shadow to a 3D Column Chart so that appears light source is from behind left.

Workbook of all work to date is attached

Thanks!
Regards,
marc

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
Regards,
marc

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 range.name

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
Regards,
marc

Option Explicit


Sub PrintNamedRange()
    Dim wb As Workbook
    Dim wsPrnt As Worksheet
    Dim lngRows As Long
    Dim nName As Name
    Dim strName As String
    Dim rngPrint As Range
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    'Initialize
        Set wb = ThisWorkbook
        Set wsPrnt = wb.Worksheets("prnt")
        lngRows = wsPrnt.Range("B65536").End(xlUp).Row
        strName = wsPrnt.Range("B" & lngRows).Value
    
    'Test
        Debug.Print strName
        
    'Select Range & Print
        Set rngPrint.Name = strName
        rngPrint.Select
        ActiveSheet.PageSetup.PrintArea = rngPrint.Address
        ActiveSheet.PrintOut
    
    'Cleanup
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .Calculation = xlCalculationAutomatic
        End With
    
        Set wb = Nothing
        Set wsPrnt = Nothing
        Set rngPrint = Nothing
    
End Sub

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
marc

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:

Quote

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
-marc

Full code:

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

Option Explicit


Sub GetNamedRange()
    Dim nName As Name
    Dim wb As Workbook
    Dim wsPrnt As Worksheet
    Dim Row As Long
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set wsPrnt = wb.Worksheets("prnt")
    Row = 3
    
    
    With wb
        For Each nName In .Names
            wsPrnt.Range("B" & Row) = nName.Name
            Row = Row + 1
        Next
    End With
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set nName = Nothing
    Set wb = Nothing
    Set wsPrnt = Nothing
    
End Sub

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

Option Explicit


Sub PrintNamedRange()
    Dim wb As Workbook
    Dim wsPrnt As Worksheet
    Dim lngRows As Long
    Dim rngPrint As Range
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set wsPrnt = wb.Worksheets("prnt")
    lngRows = wsPrnt.Range("B65536").End(xlUp).Row
    Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value
    rngPrint.PrintOut
    
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set wb = Nothing
    Set wsPrnt = Nothing
    Set rngPrint = Nothing
    
End Sub

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

Thanks Andy!
Really appreciate the help

Best regards
-markc

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)

Thanks
Best regards,
-markc

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

Thanks
Best regards,
-markc

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,
-markc

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

Thanks
Best regards,
-markc

Re: Test x Conditions In Conditional Formatting

Thanks Dave,
Works great!

Best regards,
-markc

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:
=IF(AND($J2=0,$I2>0))
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

TIA
Best regards,
-markc

Re: Toggle Formula Reference

Thanks Daddylonglegs,

Works great!

Best regards,
-markc

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

Thanks for your help
Sorry my description of the problem didn't lay it out better

Best regards,
-markc

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.

Thanks
Best regards,
-markc

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

Thanks guys!
Bill,

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,
-markc

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,
-marc

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
-marc