Posts by markc

markc · Sep 11th 2007

Re: Value Data Label Overlaps With Another Series

Thanks Andy -

The benchmark line is in front of the columns.
The workbook is too large to post
Please PM me with your e-mail address if you would like to see the workbook

I've attached a sample of a chart where the data value label overlaps the benchmark line

Thanks
-marc

markc · Sep 11th 2007

Re: Swap Series In Stacked Area Chart

Thanks Andy
I haven't been able to get the workbook small enough to post
PM me with your e-mail address if you would like to see the workbook

I have attached a sample chart where I think it would look better to move 2007 (Red) in front of 2006 (Blue).

This is an interactive chart, where user only sees 1 chart at a time. I agree, if there were multiple reports it may be confusing to jump back and forth.

Thanks
-marc

markc · Sep 11th 2007

Hi all -

I have one series on my chart set as column type with data value labels
I have another series as line type to create a benchmark line across the chart
The chart is interactive where user can select 1 of 20 units.
On some of the units, the labels are overlapping the line type chart and causing a poor display.

Any ideas on how to circumvent this problem?
Thanks
-marc

markc · Sep 11th 2007

Hi all -

I have an interactive chart displaying stacked area charts for 2006 and 2007
I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006

Can this be done using vba or formula?

Thanks
-marc

markc · Sep 10th 2007

Hi all -

I am attempting to stack 2 are charts on top of one another
It seems to me the only way I could do this is to:

anchor the charts into respective cells so same size
format second chart so all plot area, axes formatted invisible
Use the camera tool to take image of the cell with second area chart
Align the image over the first area chart

Works well, except there appear to be some small grey bars in the image that should not be there.
Do you have any idead why this is happening and how to remove them?
Any other suggestions to improve the charts would be most welcome as well

Please PM me is you would like the sample workbook
Zip is 126KB

Thanks
-marc[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Solved.

I just had to choose the correct Area Chart Subtype to get stacked area charts (one in front of another) The left upper most chart subtype in the area charts gave me what I needed

Thanks
-marc

markc · Sep 10th 2007

Re: Print Non Contiguous Ranges

Thanks Dave -

Sorry for the delay
Just back from well deserved vacation :thumbcoo:

I ended up with work-around
I setup preformatted workbook
Copy the desired ranges from the data source to the final report

A long way from where I started, but works well for me

Thanks!
-marc

markc · Aug 29th 2007

Re: Print Non Contiguous Ranges

Thanks Dave
Fortunately I wasn't connected to the printer when I first ran the macro
The 153 pages is an error
The expected output should be 4 pages at most

Thanks
-marc

markc · Aug 29th 2007

Hi all -

I would like to print a report with noncontiguous columns side by side as defined in this range

Code

With wsMarket
        Set rngPrint = Union(.Range("D3:D" & lngRows), .Range("O3:O" & lngRows), _
            .Range("Z3:Z" & lngRows), .Range("AK3:AK" & lngRows), .Range("AV3:AV" & lngRows), _
            .Range("BG3:BG" & lngRows), .Range("BR3:BR" & lngRows), .Range("CC3:CC" & lngRows), _
            .Range("CN3:CN" & lngRows), .Range("CY3:CY" & lngRows), .Range("DJ3:DJ" & lngRows), _
            .Range("DU3:DU" & lngRows), .Range("EF3:EF" & lngRows), .Range("EQ3:EQ" & lngRows), _
            .Range("FB3:FB" & lngRows), .Range("FM3:FM" & lngRows), .Range("FX3:FX" & lngRows))
    End With

The page setup is defined as landscape 1 page wide as many pages tall as need to print 256 rows. But in my first test this report produced 153 pages. I would like 3-4 max. 2 would be better.

Full code below
TIA
-marc

Code

Sub PrintRates()


    Dim wb As Workbook
    Dim wsMarket As Worksheet
    Dim rngPrint As Range
    Dim lngRows As Long
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    
    Set wb = ThisWorkbook
    Set wsMarket = wb.Worksheets("Market")
    lngRows = 256
    With wsMarket
        Set rngPrint = Union(.Range("D3:D" & lngRows), .Range("O3:O" & lngRows), _
            .Range("Z3:Z" & lngRows), .Range("AK3:AK" & lngRows), .Range("AV3:AV" & lngRows), _
            .Range("BG3:BG" & lngRows), .Range("BR3:BR" & lngRows), .Range("CC3:CC" & lngRows), _
            .Range("CN3:CN" & lngRows), .Range("CY3:CY" & lngRows), .Range("DJ3:DJ" & lngRows), _
            .Range("DU3:DU" & lngRows), .Range("EF3:EF" & lngRows), .Range("EQ3:EQ" & lngRows), _
            .Range("FB3:FB" & lngRows), .Range("FM3:FM" & lngRows), .Range("FX3:FX" & lngRows))
    End With
    ShowAll
    BeforePrint
    
    '=========================================================================
    '/Print Process
        With Sheets("Market")
            .PageSetup.PrintArea = rngPrint.Address
        End With
        
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
        
    '/End Print Process
    '=========================================================================


    AfterPrint
    wsMarket.Range("A1").Select
    
    Set wb = Nothing
    Set wsMarket = Nothing
    Set rngPrint = Nothing
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
    
End Sub

Display More

ShowAll

Code

Sub ShowAll()
Dim wb As Workbook
Dim shtMarket As Worksheet
Dim rngAurora As Range
Dim rngFull As Range


With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


Set wb = ThisWorkbook
Set shtMarket = wb.Worksheets("Market")
Set rngFull = shtMarket.Columns("D:IV")
rngFull.EntireColumn.Hidden = False


With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With




End Sub

Display More

Before Print

Code

Private Sub BeforePrint()
    Dim wb As Workbook
    Dim wsMarket As Worksheet
    Dim rngHeader As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set wsMarket = wb.Worksheets("Market")
    Set rngHeader = wsMarket.Range("A1:FH1")
    
    With rngHeader
        .Interior.ColorIndex = 1
        .Font.ColorIndex = 39
    End With
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set wb = Nothing
    Set wsMarket = Nothing
    Set rngHeader = Nothing
    
    '    Range("A1:FH1").Select
    '    Selection.Interior.ColorIndex = 1
    '    Selection.Font.ColorIndex = 39
    '    Range("A1").Select
End Sub

Display More

After Print

Code

Private Sub AfterPrint()


    Dim wb As Workbook
    Dim wsMarket As Worksheet
    Dim rngHeader As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    Set wb = ThisWorkbook
    Set wsMarket = wb.Worksheets("Market")
    Set rngHeader = wsMarket.Range("A1:FH1")
    
    With rngHeader
        .Interior.ColorIndex = 36
        .Font.ColorIndex = 1
    End With
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Set wb = Nothing
    Set wsMarket = Nothing
    Set rngHeader = Nothing


    '    Range("A1").Select
    '    Range(Selection, Selection.End(xlToRight)).Select
    '    Selection.Interior.ColorIndex = 36
    '    Selection.Font.ColorIndex = 1
    '    Range("A1").Select
End Sub

Display More

markc · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Hi Norie -

Edited by marc -
Weird. How did same message post? See above.
The sample is to copy data from a single sheet in a workbook to a single sheet in a new workbook
Once I got the procedure working I will copy it down and simply update the sheet name for the new snippet
Ex. Change Grumpy to Doc.
It is not the most efficient way to program
But since I don't have total control of the workbook, I think it might be the best solution

Thanks
-marc
=======

Wow! Long time haven't heard from you :smile:

The code is the start for 7 other sheets in the workbook
Once I get it working properly it's just a matter of copy/paste the code and update the sheet names for all 8 worksheets to be copied out to a new workbook.

Thanks
-marc

markc · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Hi Norie -
Wow! Long time haven't heard from you :smile:

The code is the start for 7 other sheets in the workbook
Once I get it working properly it's just a matter of copy/paste the code and update the sheet names for all 8 worksheets to be copied out to a new workbook.

Thanks
-marc

markc · Aug 12th 2007

Re: Pass Row Number Of Last Used Column Row to Variable

Thanks guys -

Shear brilliance on my part trying to use offset on a variable dim'd as long:exclamat:
The revised code below accomplishes exactly what I need

Thanks much!
-marc

Code

Option Explicit


Sub Disney_DumpData()
    Dim wbBook As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim rng As Range
    Dim rngNew As Range
    Dim rngUnit As Range
    Dim rngUnitPaste As Range
    Dim lngNew As Long
    Dim Cnt As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False


    Set wbBook = ThisWorkbook
    Cnt = 0
    
    'Dump To New File
    
        '/Define new workbook
            Set wbNew = Application.Workbooks.Add
            wbNew.SaveAs "C:\data\delete\Disney_budget_data.xls"
            wbNew.Sheets("Sheet1").Name = "Data"
            Set wsNew = wbNew.Worksheets("Data")
            ActiveWindow.Zoom = 75
            
        '/Copy Grumpy
            '/Data
                Set ws = wbBook.Worksheets("Grumpy")
                Set rng = ws.Range("A14:X164")
                rng.Copy
                If Cnt = 0 Then
                    lngNew = wsNew.Range("B65536").End(xlUp).Row
                Else
                    lngNew = wsNew.Range("B65536").End(xlUp).Row + 1
                End If
                Set rngNew = wsNew.Range("B" & lngNew)
                rngNew.PasteSpecial (xlPasteValuesAndNumberFormats)
            '/Unit Number
                Set rngUnit = ws.Range("B3")
                rngUnit.Copy
                If Cnt = 0 Then
                    Set rngUnitPaste = wsNew.Range("A" & lngNew, "A" & lngNew + 150)
                Else
                    Set rngUnitPaste = wsNew.Range("A" & lngNew + 1, "A" & lngNew + 151)
                End If
                rngUnitPaste.PasteSpecial (xlPasteValuesAndNumberFormats)
            '/Cleanup - Setup
                Set ws = Nothing
                Set rng = Nothing
                Set rngUnit = Nothing
                Set rngUnitPaste = Nothing
                Set rngNew = Nothing
                Cnt = 1


    '/Cleanup
    
        Set wbBook = Nothing
        Set wbNew = Nothing
        Set ws = Nothing
        Set wsNew = Nothing
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
        
        
            
End Sub

Display More

markc · Aug 12th 2007

Hi all -

This line in my code is causing an invalid qualifer error message:

Code

lngNew = wsNew.Range("B65536").End(xlUp).Row.Offset(1, 0)

I am unsure why. It seems it should work

Full code below
Thanks
-marc

Code

Option Explicit


Sub Disney_DumpData()
    Dim wbBook As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim wsNew As Worksheet
    Dim rng As Range
    Dim rngNew As Range
    Dim rngUnit As Range
    Dim rngUnitPaste As Range
    Dim lngNew As Long
    Dim Cnt As Integer
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False


    Set wbBook = ThisWorkbook
    Cnt = 0
    
    'Dump To New File
    
        '/Define new workbook
            Set wbNew = Application.Workbooks.Add
            wbNew.SaveAs "C:\data\delete\Disney_budget_data.xls"
            wbNew.Sheets("Sheet1").Name = "Data"
            Set wsNew = wbNew.Worksheets("Data")
            ActiveWindow.Zoom = 75
            
        '/Copy SLC
            '/Data
                Set ws = wbBook.Worksheets("Grumpy")
                Set rng = ws.Range("A14:X164")
                rng.Copy
                If Cnt = 0 Then
                    lngNew = wsNew.Range("B65536").End(xlUp).Row
                Else
                    lngNew = wsNew.Range("B65536").End(xlUp).Row.Offset(1, 0)
                End If
                Set rngNew = wsNew.Range("B" & lngNew)
                rngNew.PasteSpecial (xlPasteValuesAndNumberFormats)
            '/Unit Number
                Set rngUnit = ws.Range("B3")
                rngUnit.Copy
                Set rngUnitPaste = wsNew.Range("A" & lngNew + 1, "A" & lngNew + 152)
                rngUnitPaste
            '/Cleanup - Setup
                Set ws = Nothing
                Set rng = Nothing
                Set rngUnit = Nothing
                Set rngUnitPaste = Nothing
                Set rngNew = Nothing
                Cnt = 1


    '/Cleanup
    
        Set wbBook = Nothing
        Set wbNew = Nothing
        Set ws = Nothing
        Set wsNew = Nothing
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True

Display More

markc · Jul 29th 2007

Re: SUMPRODUCT Formula Returns Error

Thanks
Checked again
There is #N/A in col P
I guess it did not show in the Go To Special....Error because at some point I pasted the lookup formula as a value

Thanks for your help!
-markc

markc · Jul 29th 2007

Re: SUMPRODUCT Formula Returns Error

Thanks
The array formula does return the expected result.
Still curious why the sumproduct does not work though?

Thanks
-markc

markc · Jul 29th 2007

Re: SUMPRODUCT Formula Returns Error

Thanks parsnip
No.
I scrolled through all data and used Edit..Go To...Special...Formulas...Errors
"No Errors Were Found"

There are no blank cells in the range.
Thanks
-markc

markc · Jul 29th 2007

Re: Array Formula Returns Error

Thanks
It is not committed with (ctrl+shift+enter)
How can I change the title?

Thanks
-markc

markc · Jul 29th 2007

Hi all -

I have sumproduct formula that returns #N/A error and I'm not sure why
=SUMPRODUCT((YTD!$A$2:$A$2599=B$10)*(YTD!$P$2:$P$2599=$Q19)*(YTD!$E$2:$E$2599))

If I narrow the range from $939 to $1093 then the formula works.
But that is only evaluating 1 unit of 10
As I add worksheets to the workbook and update the unit numbers in B10, the entire range needs to be evaluated for matching criteria.

Any ideas?
Sorry, workbook is way too large to upload.
TIA
-markc

markc · Jul 20th 2007

Re: Remove Superfluous Spaces

Thanks all

Richard, I'll give the code a go later and come back
My other thought was to use a series of replace statements
Each file is small and there are no more than 10 sets of strings that recur in exactly the same manner

Example:
Bread & Ro lls Co st
Bread & Ro lls Co st
Bread & Ro lls Co st
Bread & Ro lls Co st
Bread & Ro lls Co st

I will use a series of replace statements to correct the output
Thanks for your help.

-marc

markc · Jul 20th 2007

Hi all -

I convert a .pdf report to excel using Able2Extract
The output is leaving some xtra spaces inside the word

For example,
Clean in g Co st

I tried running Dave McRitchie's Trimall on the text, but no luck

Sample workbook attached
Thanks
-marc

markc · Jul 18th 2007

Re: Convert Sumproduct To DSUM

Thanks much Lee!
I see what you are saying that sumproduct may be the way to go

At least I understand DSUM better now and can incoporate in other worksheets

Thanks again for your assistnace and hard work!
-marc

Posts by markc

Value Data Label Overlaps With Another Series

Swap Series In Stacked Area Chart

Value Data Label Overlaps With Another Series

Swap Series In Stacked Area Chart

Chart With Camera Results In Additional Image

Print Non Contiguous Ranges

Print Non Contiguous Ranges

Print Non Contiguous Ranges

Pass Row Number Of Last Used Column Row to Variable

Pass Row Number Of Last Used Column Row to Variable

Pass Row Number Of Last Used Column Row to Variable

Pass Row Number Of Last Used Column Row to Variable

SUMPRODUCT Returns N/A Error

SUMPRODUCT Returns N/A Error

SUMPRODUCT Returns N/A Error

SUMPRODUCT Returns N/A Error

SUMPRODUCT Returns N/A Error

Remove Superfluous Spaces

Remove Superfluous Spaces

Convert Sumproduct To DSUM