Posts by p45cal

Re: Convert Date To Text

Rather than post a sample of imported data, how about posting a file (or a part of one) that you want to import, then I or someone can show a way of importing it without excel doing it conversions to dates?

p45cal

Re: Vba Chart Axes Editing Syntax

Maybe it's Office2007 and perhaps Vista which is the problem, I feel I read somewhere there's a big difference in Excel charts with 2007. In case it helps, I recorded a macro in XP with Office 2003 (both of which I'm sticking to even though I can 'upgrade' to both of them at no cost to me) and ascertained that:
ActiveChart.Axes(xlCategory).Border.ColorIndex = 3
will turn the x-axis red, and
ActiveChart.Axes(xlValue).Border.ColorIndex = 4
will turn the y-axis green.

p45cal
(hope the code tag police don't catch me!)

Re: Combine Columns, Intercalate

You beat me to it, but to add, just in case the columns' top left isn't in cell A1, then a more general formula can be applied, in this case for top left being H9:
Put the following in cell J9 and copy down:
= OFFSET($H$9,INT((ROW()-ROW($H$9))/2),MOD(ROW()-ROW($H$9),2))

p45cal

Re: Vba Chart Axes Editing Syntax

Your best bet is to record a macro while you're changing a chart in the way that you would like to make editable. Then examine the resultant code. I did just this and changed the plot order of the x-axis and the max and min of the y-axis.This is the result:

ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        .CrossesAt = 1
        .TickLabelSpacing = 1
        .TickMarkSpacing = 1
        .AxisBetweenCategories = True
        .ReversePlotOrder = True
    End With
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 3
        .MaximumScale = 20
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With

from which I can see that:
ActiveChart.Axes(xlCategory).ReversePlotOrder = True
reverses the plot order, and
ActiveChart.Axes(xlCategory).ReversePlotOrder = False
puts it back, and that
ActiveChart.Axes(xlValue).MinimumScale = 3
sets the minimum y axis to 3, while
ActiveChart.Axes(xlValue).MaximumScale = 20
sets the maximum to 20

etc. etc.

p45cal

Re: Dynamic Pie Chart With No Zeros

For bar charts and others it's easy; select the chart then go to the dropdown menus, Tools|Options, then on the Chart tab check the checkbox 'Plot visible cells only'. I said it was easy, not intuitive.
p45cal

Re: Last Used Cell in Column

I think Dave meant my second post, not the first.
To stop the error, drop the DIM statement (I didn't include it).
p45cal

Re: Referencing A Field

Not sure if this as useful in this form, nonetheless:
this will give an R1C1 type reference string:

ende = Cells(Rows.Count, 1).End(xlUp).Address(ReferenceStyle:=xlR1C1)

and this an A1 type reference string:

ende = Cells(Rows.Count, 1).End(xlUp).Address

p45cal

Re: Referencing A Field

on my xl 2003 the rowcount line returns the lowest row from among all columns, not just column A, not being picky, just be aware..

p45cal

Quote from jetted

Code

Sub test()
    'find last cell used in column A
    rowcount = [A:IV]. Find("*", [A:IV].Item(1, 1), , , _ 
    xlByRows, xlPrevious).Row 
    'go to the last cell
     Range("A" & rowcount).Select 
    'take the address of the last cell
    ende = ActiveCell.Address(rowabsolute, columnabsolute) 
    'from there you can use this reference (ende as the last range in column A)
End Sub

Re: Referencing A Field

how about fro column A:

Dim ende As Range
Set ende = Cells(Rows.Count, 1).End(xlUp)

p45cal

Re: Referencing A Field

at its simplest:

Dim ende As Range
Set ende = Range("A4160")

p45cal

Re: Dynamic Pie Chart With No Zeros

See attached. I think this does what you're looking for. See code in the worksheet code module which runs the macro 'Blah' (in a standard code module) every time the value in cell BC3 changes. B3 has a data validation dropdown which uses a dynamic range (so that additional months added will work too) to select the month.

It's true that with pie charts it's difficult not to plot 0 values, unlike other chart types. The code to clean up the chart is plagiarised and adjusted from the Sub CleanUpActiveChartLabels() section here:
http://pubs.logicalexpressions…009/LPMArticle.asp?ID=390

The chart itself uses a named range called 'theChart' which is dynamically derived from the value of BC3. You can see evidence of this if you click on a segment of the chart then look in the formula bar where you will see the likes of:
=SERIES(,Sheet1!$A$3:$A$19,'pie chart example_pd02.xls'!theChart,1)

I came across this too:
http://www.andypope.info/charts/piezeros.htm
which might be of interest.

p45cal

Re: Use Count Property To Return Number Of Cells With Values

Then I guess that would be

r = Evaluate("count(g3:g10)")

or

r = Application.Count(Range("g3:g10"))

(not tested)
p45cal

Re: Chart Scale Auto Update

Either a small Macro

Sub blah()
Sheets("Sheet1").ChartObjects("Chart 2").Chart.Axes(xlValue).MinimumScale = Range("c3")
End Sub

or in the code module for the worksheet (accessed by right-clicking the sheet tab, choosing 'View code' and pasting this where the cursor is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("C3")).Address = Range("c3").Address Then ChartObjects("Chart 2").Chart.Axes(xlValue).MinimumScale = Range("c3")
End Sub

(It's already in the attached, but needs enabling by removing the comment character (a single quote mark) at the start of each of the 3 lines)
What this does is to detect whether the cell A3 is being modified, and if it is then set the chart axis start date to that date.

The button on the sheet calls the other small macro (which is also in the attached).

Re: Copy Picture - System Resources Error

Now just how did you get to reinstalling the printer driver from "insufficient system resources"?
LOL

p45cal

Re: Copy Picture - System Resources Error

I've done this without a problem. Could you post your code?

p45cal

Re: Range().select

Range("A" & I & ":E" & I)

does work! What erroe is being thrown up?

however some other possibilities:

Range(Cells(I,["A"]),Cells(I,["E"])).select
Range("A1:E1").offset(I-1,0).select

p45cal

Re: Macro For Lookup & Return 3 Times

Not quite sure what you wanted but see attached a version of workbook2.xls (zipped) with a button which calls a macro. Try it and see if this is what you wanted to happen.
p45cal

Re: Copy Value/format; No Screen-flickering

I've tried your code out, setting up a worksheet with dummy data in it and several sheets.
I get NO problems with taking both Select statements out, regardless of which sheet I start on, regardless of how many times I call the sub, with M set to the same value or to different values.
I wrote a sub:

Sub test()
Application.ScreenUpdating = False
copySample 3
copySample 4
Application.ScreenUpdating = True
End Sub

which, after taking out the

Application.ScreenUpdating = True

(surely I should be able to get away with not putting code tags around such a short piece of code?)
line at the end of your copySample sub, ran without screeen flicker.
BTW, (at least with Excel2003), when you use vba to pastespecial across diferent sheets, the respective sheets ARE activated/deactivated without asking you; you don't need to select anything, and whichever sheet was the active sheet beforehand, remains so afterwards.

Can you confirm it is XL2003 you're using as in your profile, not XL2007?

Regarding "copy both the values and the format in one line"
There is a xlPasteAll constant which might fit the bill. There are other constants you can use; if you delete 'xlPasteValues' and type in a minus sign (-) you will get a list of possibilities in a dropdown (at least on mine it did!).

I played around looking at the values of these constants and tried adding their values, but only succeeded in crashing Excel.

p45cal

Re: Autofilter Copy Blank Row

This one does away with the requirement for the 'temp' sheet:

Sub blah()
For Each cll In Sheets("Current").Range("A1").CurrentRegion. _
Columns(1).SpecialCells(xlCellTypeConstants, 3).Cells
If cll.Value = "New Entry" Then
    With Sheets("New Entry")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        cll.EntireRow.Copy .Cells(LastRow, 1).EntireRow 'does it have to be the whole row?
        .Cells(LastRow, 1) = Date
    End With
End If
Next cll
End Sub

P45cal

Re: Highlight Range If There Are No Numbers

Taking row 5 as an example, in any cell (or as many as you like, but you can copy conditional formatting so it's easier to just start with one cell) ON ROW 5, go into conditional formatting and choose the 'Formula Is' option, then enter this formula:
=COUNT($B5:$L5)=0
choose a format to your liking and click OK.
This will check that ALL the cells in columns B to L in row 5 contain no number.

If you wanted the highlight to come into play if there was at least one number missing from the same range, the formula would instead be:
=COUNT($B5:$L5)<>11

If you copy these formulae as they are, retaining the $s in place, you can then copy the conditional formatting both across and/or down, using the Format Painter or Copy, Paste Special, Formats.