Posts by Robert B

Re: Chart with non-contiguous X an Y Values

Hi Andy

thanks again for your interest. I have attached a sample of the data. Changing the data source manually is no problem, but how do I identify the data range in a macro? If I say

Set myRange = Range(LocationRange,DataRange) 'with/without "s
        ActiveChart.SetSourceData Source:=myRange, PlotBy:=xlColumns

all the current range is included

Regards

Robert

Hi all,

I have a chart which represents the values for the last 3 months for a range of locations. The location name is in column 1, the values change from month to month by 1 column right. I use

Set DataRange = Range(Activecell.Offset(0,-2), Activecell.Offset(20,))
Set LocationRange = Range(R20C1:R40C1)

to establish the X and Y values, but the chart appears either with all months or, if I leave the Location out, offers the column next to the first month's data as X values.

I have got round this for the moment by

ActiveChart.SeriesCollection(n).delete

an appropriate number of times, but this can't be good.

Can somebody suggest a solution?

Regards

Robert

Re: Amending Chart location and size

Thanks again Andy, its a winner!

Cheers

Robert

Re: Amending Chart location and size

Hi Andy

Thanks very much, that's perfect. May I ask a further question? Is it possible to place a chart at a particular cell, or range, location as it seems that the chart creation occurs at variable points on the page and so moving an absolute distance gives a final position that is incorrect.

Thanks again

Robert

Hi All

I have craeted a chart in VBA, which sits in the middle of the page . I have attempted to move it to a new location with the following code

ActiveChart.Parent.Name = "MyChart"
   
        ActiveSheet.ChartObjects("MyChart").IncrementLeft -237.75
    ActiveSheet.ChartObjects("MyChart").IncrementTop 32.25
    ActiveSheet.ChartObjects("MyChart").ScaleHeight 0.96, msoFalse, msoScaleFromTopLeft
    ActiveSheet.ChartObjects("MyChart").ScaleWidth 1.17, msoFalse, msoScaleFromTopLeft

which I adapted from a recorded macro, but which fails in execution, error message indicates that the Object does not support the property or Method.

can someone see where Iam going wrong, please?

Regards

Robert

Re: Pivot Tables subColumn Removal

A quick update, Derk, a perfect result

This is just what I needed, thanks very much indeed.

Is this documented anywhere or is it something that one learns by experience

Kind Regards

Robert

Re: Pivot Tables subColumn Removal

Hi Derk

is the number of 'False' s significant.

Sorry the attachment didn't work.

Many thanks
Robert

Hi All

I attach a file containing data and table created by this code

Dim myPivot As PivotTable
Dim myField As PivotField
Dim myItem As PivotItem
Application.ScreenUpdating = False
Selection.CurrentRegion.Select
Set myRange = Selection
    ActiveWorkbook.Names.Add Name:="Database", RefersTo:=myRange
Set myPivot = ActiveSheet.PivotTableWizard
    Set myField = myPivot.PivotFields("AC")
    myField.Orientation = xlDataField
    myField.Function = xlCount
    myField.Name = "Count"
    
    Set myField = myPivot.PivotFields("XX")
    myField.Orientation = xlRowField
    myField.Name = "LOS"
    Set myField = myPivot.PivotFields("Day")
    myField.Orientation = xlColumnField
    myField.Name = "Day"
    Set myField = myPivot.PivotFields("Category")
    myField.Orientation = xlColumnField
    Set myField = myPivot.PivotFields("Type")
    myField.Orientation = xlPageField
    myPivot.ShowPages
    ColumnGrand = True

How do I inhibit the daily totals as shown in the attached.
My current solution is to Copy Paste values and delete the subtotal columns, I hope there is a VBA solution.

Many thanks

Robert

Re: Pivot Table Column Heading Order

Thanks for all your help Andy and MD7, the problem was, in the end, quite simple.

I appreciate all the input

Thanks again

Robert

Re: Pivot Table Column Heading Order

Hi Andy,

Thats the sort of thing that I find really irritating, that the same code runs differently on different computers. In that situation the chances of problem resolution become increasingly remote. I attach a small file as a sample of my data and hope that the problem lies there, rather than in my implementation of Excel/Windows.
Regards

Robert

Re: Pivot Table Column Heading Order

Thanks Roy, MD7 and Andy

In reverse order; Andy the source data is formatted as Time style hh:mm; MD7 your suggestion would work perfectly well but I feel there must be a single command solution e.g. ptField.NumberFormat = something (("hh:mm:ss") does not succeed). Roy, here is a sample of the code, some names have been changed to protect the guilty.

Dim myPivot As PivotTable
Dim myField As PivotField
Dim myItem As PivotItem
Dim myRange As Range


Selection.CurrentRegion.Select
Set myRange = Selection
    ActiveWorkbook.Names.Add Name:="Database", RefersTo:=myRange
Set myPivot = ActiveSheet.PivotTableWizard
    Set myField = myPivot.PivotFields("XX")
    myField.Orientation = xlDataField
    myField.Function = xlCount
    myField.Name = "Count"
    
    Set myField = myPivot.PivotFields("R")
    myField.Orientation = xlRowField
    myField.Name = "Spec"
    Set myField = myPivot.PivotFields("RR")
    myField.Orientation = xlRowField
    myField.Name = "Specialty"
    Set myField = myPivot.PivotFields("RRR")
    myField.Orientation = xlRowField
    myField.Name = "Column"
    Set myField = myPivot.PivotFields("Hour")
     myField.Orientation = xlColumnField

Thanks for your interest

Robert

Hi All

I have a macro which creates a pivot table with hours of the day as column headings. The order in which the pivot presents the data is 0:00, 1:00, 10:00, 11;00......19:00, 2:00, 20:00 etc. How can I arrange for the columns to be sequential

Thanks
Robert

Re: Failed criteria in Autofilter

Thanks Dave, that's brilliant
Regards
Robert

Re: Failed criteria in Autofilter

Hi Dave

thanks for the response, unfortunately I get exactly the same result. I tried changing the filtered column to text format but that produced serial date numbers so thats no good. What I can't understand is why the command works manually with both date and string format for the criteria but fails in the macro. Could there be something else, outside this command, that could be affecting it?

Regards

Robert

Hi All

I have used the following code

Selection.AutoFilter Field:=4, Criteria1:=">=" & myLDate, Operator:=xlAnd _
        , Criteria2:="<=" & myDate

where myLDate and myDate are declared variables and each shows a correct value in the Locals window. When this section of the code runs it finds no records, although I know that there are around 1500. When I stop the code at this point and look at the sheet in question , the variable is still set and, if I filter manually all the expected records are selected. Can someone see where the failure lies?

Thanks

Robert

Re: COUNTIF parameter

Hi Norie

thanks a million, its just perfect

Robert

Hi All

I am trying to count the occurences of numbers greater than zero in a column

I thought this might work but it seems to contain the wrong number of arguments

Can somebody say where I am going wrong

x = Application.WorksheetFunction.CountIf(ActiveCell.Offset(1, 0), ActiveCell.Offset(r, 0), 0)

where x is an integer and r is the end Row number.

Thanks

Robert

Re: Fill down in VBA

This routine will copy the contents of a cell or range of cells into blank cells or range of cells

Range("cella:cellb").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Formula = "=R[-1]C"

hope this is of use

Robert

Re: Referencing an External Workbook

Hi Roy

Sorry, missed the point. Yes, there is a Sheet1 in the target workbook

Robert

Re: Referencing an External Workbook

Hi Roy

the workbook I am trying to access has 12 sheets, each of which is named, however, for the reasons mentioned earlier, I would like to access them by their index. If I had the workbook in question open and active I could refer to the sheets by "Sheets(1)" and this is what I want to do when it is inactive and closed. There is not a sheet tabbed "Sheet1"

Apologies for the confusion

Robert