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?


    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.

    (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))


    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:

    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.


    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.

    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


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


    Quote from jetted
    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: 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:

    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:
    which might be of interest.


    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: Range().select

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

    does work! What erroe is being thrown up?

    however some other possibilities:



    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.


    Re: Autofilter Copy Blank Row

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


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

    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.