Posts by dkabambe

    I don't think I explained myself.

    When I use NA() Excel leaves a gap in the sense that it does not plot that particular point, but the line chart directly connects the points either side.

    However, when the cell is genuinely blank Excel leaves a real gap - in effect the chart looks like two seperate lines, and this is the effect I wish to replicate.

    My problem is I am plotting a data series which I want to be like such:

    =IF(A1=condition, A1, leave gap)

    and can't work how to do this without using VBA, or if it is not possible. (i.e. I have a data series in row 1, but where the data points meet certain conditions I want Excel to leave a gap even though there is data in the original series.)

    Also I don't know how mnay such gaps there will be, so I can't just seperate the data into seperate series and plot them that way.

    Thanks, Dzinja

    If I plot a line chart on a series of data and leave a blank in the row the graph leaves a corresponding gap in the line.

    I am trying to replicate this effect when the data series comes via formulae. I have tried getting the formula to return an empty string ("") and various error values but in each case the graph either directly connects the adjacent values or connects them via 0.

    Anyone any ideas? (without using VBA)

    I have a spreadsheet project which produces various reports. I have a macro which copies the active report into a new book, simultaneously hard-coding any numbers from external sources whilst keeping formulae intact. The purpose of this is so I can email the reports without the (very large) underlying data files. This is the code I have so far:

    ActiveWorkbook.ActiveSheet.Copy    'NB: Problem when cells have more than 255 characters
    If Not IsEmpty(ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)) Then
        For Each linkName In ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
            ActiveWorkbook.BreakLink Name:=linkName, Type:=xlLinkTypeExcelLinks
    End If

    This works fine for the spreadsheet reports, and the charts where the series are hard linked to specific ranges. However, some of the charts use dynamic ranges and it is when exporting these the macro trips up. It converts the ranges in the series formulae to (non-existent!) equivalent ranges in the newly created workbook, as opposed to hard-coding the data values into the series formulae. Anyone any ideas how to accomplish this?

    Thanks in advance, Dzinja

    I also used to regularly get this problem on my old computer, (running Office2K on Win98).

    In my case I found the following sequence usually solved the trick:
    - Press down left mouse button
    - Press down right mouse button
    - Release left button
    - Release right button
    I have no idea why but I assume to do with sticky buttons. Later I started having the same problem start occuring with my keyboard. A similar sequence with the left CTRL and left SHIFT keys usually solved that.

    Assuming I understand what you're trying to do there are several ways. You can use a For..Next loop instead of Do..Loop with minimal change to your current code:

    For i = 2 To 50
    Sheets("All Data").Select
    Range("D" & i).Select
    Sheets("All Trip Sheets").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

    Simply call the onTime method again with the same parameters, but set the schedule parameter to FALSE. For your example use this code to set the timer:

    exitTime = Now+TimeValue("00.05.00")
    Application.OnTime exitTime, "timeup"

    and the following line

    Application.OnTime exitTime, "timeup",,False

    to cancel the timer. [Remember to make the ExitTime variable public/shared as necessary depending on where the stop code will be executed]

    HTH, Dzinja

    I want to be able to determine if a particular cell has precedents on another sheet, or in another workbook. I had hoped to use .Precedents and do something like comparing "testCell.Precedents.Count" with "Intersect(Cells,testCell.Precedents).Count", or looking for "!" in testCell.Precedents.Address.

    However the .Precedents range only contains precedents on the same sheet, so this doesn't work. I have tried a couple of other tricks but so far can't find a catch-all workaround, (mainly because I some of the formula reference named cells on other sheets).

    Any help would be greatly appreciated, Dzinja.

    I have various spreadsheets and bits of spreadsheets in Excel that need to go into a Word document.

    I want to paste these as Word tables, simultaneously converting all the formulae to their values - but preferably keeping the border settings although this is purely optional.

    I'm probably being rather dense but what's the easiest way?

    You could just try adding the controls you need rather than adding everything and hiding the ones you don't...

    HTH, Dzinja

    The first thing that springs to mind is to add another column in TAB B before the cost 1 column which combines the width and height with a formula like
    ="W"&$A2&"H"&$B2 (in cell C2, if width/height are in cols A & B)

    and sort TAB B by this column, (which can be hidden if necessary).

    Let's Assume this is column C and the costs are in D-F. The entries for the Cost1 column in TAB A, (Row 2), would look like

    =VLOOKUP("W"&$A2&"H"&$B2,'TAB B'!$C:$F,2,TRUE)

    replacing the 2 just before the TRUE with 3 for cost 2 and 4 for cost 3.

    HTH, Dzinja