Posts by Jon Peltier

    Re: Drawing multiple charts on the same worksheet


    Just like the first series:


    Code
    .SeriesCollection.NewSeries 
                .SeriesCollection(2).XValues = "=" & Ws2.Name & "!R" & 1 & "C3:R" & 1 & "C376" 
                .SeriesCollection(2).Values = "=" & Ws2.Name & "!R" & CurrRow & "C3:R" & CurrRow & "C376" 
                .SeriesCollection(2).Name = "=" & Ws2.Name & "!R" & CurrRow & "C2"

    Re: Excel 2013 - how to prevent chart automatic formatting


    This "should" work, though I haven't used chart templates enough to know for sure. I did a simple test just now, which was promising.


    Format one of your charts exactly how you want it. Right click on the chart, click Save as Template from the pop-up menu, then give the template a descriptive name, and click Save.


    Next time you change data and the chart loses its formatting, right click the chart, choose Change Chart Type, click on Templates, select your template, and click OK.

    Re: Dynamic Named Range for Chart using INDIRECT function - does not work


    This technique does not work for making the entire source data range of a chart dynamic. Excel converts the name to a static range. You've discovered this.


    This technique is good for naming dynamic one-column or one-row ranges, and entering these as X or Y value ranges into the series formula.

    Re: Excel VBA code to change bar chart colors based on change from previous bar


    Here's an easier way, which is dynamic, and does not involve VBA.


    Let's assume you have category (X-axis) labels in column A and Y values in column B. Let's also assume row 1 has labels and the data starts in row 2. Column C will have values which exceed the previous value, and column D will have values which are less than the previous value.


    Type "Increase" in C1 and "Decrease" in D1.


    Enter =B2 in C2 and 0 in D2.


    Enter =IF(B3>=B2,B3,0) in C3, and fill it down the column.


    Enter =IF(B3<B2,B3,0) in D3, and fill it down the column.


    Select A1:A[last row], then hold Ctrl while selecting C1:D[last row].


    Insert a stacked column chart. Format the "Increase" series to use the color for an increasing value, and the "Decrease" series to use the color for a decreasing value.


    Red and Green are festive colors, but something like 8% of the male population (<1% of the female) have color vision deficiencies, and the most common is difficulty distinguishing red from green. Red and Blue work, so do Orange and cyan.

    Re: Function with an output that will not graph, but wont break functions?


    Oh sure, you're looking for NULL(), which makes the formula return a null or blank cell.


    I've been looking for it and asking MS for it for years, but unfortunately it doesn't exist.


    "Don't plot empty cells" refers only to actual empty cells without any contents (i.e., no formulas or empty "" string).

    Re: Inserting multiple series into Excel chart using VBA code


    Instead of dumping a file on us (which I'm not going to download and open), why not show us exactly how you tried to add your changing range references to the recorded code, in other words, show us the lines of code that did not work, surrounded by a few more lines to give us a little context? I'm sure someone with more experience could fairly easily determine how to correct it.

    Re: Excel - Graph multiple lines in continuous series


    A continuous line as in 48 points for 1/1/2012 followed by 48 points for 1/2/2012, etc? You need to unpivot your data so you have a column for date & time and a column for values. Then plot this in an XY chart, or a line chart that has a text (not a date) category axis.

    Re: Drawing multiple charts on the same worksheet


    It would go something like this:


    Re: Drawing multiple charts on the same worksheet


    Here is the code simplified for putting all charts onto the sheet with the data.



    You might want to position the charts, otherwise all 30 will be stacked on each other.

    Re: Disable Alt-click Opening Research Pane


    After fighting with this myself for years, I have finally found out the answer.


    From Excel, press Alt-F11 to open the VB Editor.


    Press Ctrl-G to open the Immediate Window.


    Type this line and press Enter:


    Application.CommandBars("Research").Enabled = False

    Re: VBA multiple ranges in a XY scatter chart


    Do this manually, and examine the series formulas. Write code that mimics this formula. (Note: in VBA the .Formula will have commas while the .FormulaLocal ought to have semicolons as in your examples.)


    Alternatively, set up a staging area that holds your data in the best arrangement, that is, in a continuous column instead of three adjacent columns.

    Re: Plotting graphs using macro


    How many of these 1000's of columns do you think you'll be able to plot before the chart is one big mass of points and lines?


    How is your data arranged? Presumably you have X and Y for each series.


    Rather than write something from scratch, you might try out a little (free!) utility I created some time ago, the Quick XY Chart Utility. It lets you select a range, choose the arrangement of X and Y, and produce a chart.


    Getting rid of the scientific notation is possible but nasty (see Scientific/Exponential Notation Axis Labels).

    Re: DisplayBlanksAs = xlInterpolated for One Series Only


    If you use an XY chart, use separate XY columns for each series. Blank Y values for the machine will leave a gap. Leave out any blanks for the human measurements, in other words, just have the dates for when you have measurements, and don't use dates (x values) where there are no measurements. No blanks, no gaps.


    Alternatively, don't turn on the xlInterpolate option. In the series you don't want gaps, enter #N/A errors in the blank cells. Excel automatically draws a gap across the #N/A. You can hide the error with conditional formatting.

    Re: Create Multiple Charts On Multiple Spreadsheets


    Pseudocode:


    Code
    for each ws In activeworkbook.worksheets
    for i=2 to ws.usedrange.columns.count
    set rchartdata=intersect(union(ws.columns(1),ws.columns(i)),ws.usedrange)
    set cht = ws.chartobjects.add(100,100+250*ws.chartobjects.count),325,250).chart
    cht.setsourcedata source:=rchartdata
    cht.charttype=xlxyscatter
    'other formatting
    next
    next

    Re: Inserting Textbox onto graph using VBA.


    Okay, the approach I mentioned uses Data Labels, not Text Boxes.


    To put custom text into a data label: