Posts by dkabambe

Thanks for the info - have managed to adapt for my purposes.
Dzinja

Thanks Andy - I think this is going to have to be the way forward.
Dzinja

I have got that set, but I'm using 2002 SP-2. Will try at home where I've both 97 and 2000 to make sure. Thanks, Dzinja.

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.
Dz

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)

You can use
=SUBSTITUTE("["&A3&B3&C3&D3&E3&F3&G3&H3&"]",",]","]")

in cell I2 to get the required result,
Dzinja

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
ActiveWorkbook.ActiveSheet.Unprotect
If Not IsEmpty(ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)) Then
    For Each linkName In ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks)
        ActiveWorkbook.BreakLink Name:=linkName, Type:=xlLinkTypeExcelLinks
    Next
End If
ActiveSheet.Protect

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.
Dzinja

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
Selection.Copy
Sheets("All Trip Sheets").Select
Range("H2:I3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

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

Try

Range("f65536").end(xlup).offset(1,0).formula = "=F9*-1"

instead

HTH, Dzinja

As it happens there is a much easier way to do what I was trying to do which side-steps needing to know the precedents. However, it is good to know in case I need it in future.

Many thanks, Dzinja

Instead of an extra next, put a label just before the next and a goto where you want to skip to the next in the for loop.

For Each ... in ...
If testCondition Then GoTo nextOne:

...code...

nextOne:
Next

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.

Thanks - I knew I was being stupid. My excuse was that it had been a long day and I had a cold!!

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

Set myBar = Application.CommandBars("CTSMenuBar")
    Set myControl1 = myBar.Controls.Add(msoControlPopup)
    myControl1.Caption = "&File"
    
        Set myControl2 = myControl1.Controls.Add(ID:=109)
        Set myControl2 = myControl1.Controls.Add(ID:=4)
        
        Set myControl2 = myControl1.Controls.Add(msoControlPopup)
        myControl2.Caption = "Prin&t Area"
        
            Set myControl3 = myControl2.Controls.Add(ID:=364)
            Set myControl3 = myControl2.Controls.Add(ID:=1584)

HTH, Dzinja

You can get at the registry entries from VBA with a little API calling. Have a look at the code at http://www.developerfusion.com/show/1857/ for an example.

Obviously you can alter this to get at the Product ID for excel if it is stored within the registry.
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