i've in my excel file a sheet with 3 columns (id,type,month).
knowing that many items appearing more than once, i need to get the number of occurances of each id per month then to get the number of ids appearing twice, appearing three times, ...
i was told that pivot table is the solution. so i created a pivot table with macro recorder. when i try to execute the macro it send me the error 1004: invalid references.
here my macro:
Sub Relivr() ' Dim LastRow As Long LastRow = ActiveWorkbook.Worksheets("Delivery").Range("A65536").End(xlUp).Row 'the error comes from this line ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'Delivery'!R1C1:R" & LastRow & "C4", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="'Delivery'!R1C13", TableName:="Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion12* Sheets("Delivery").Select Cells(1, 13).Select With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("ID") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _ "Month") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _ PivotTables("Tableau croisé dynamique2").PivotFields("type"), _ "Nb delivries", xlCount ActiveSheet.PivotTables("Tableau croisé dynamique2").RowGrand = False Range("H3").Select ActiveCell.FormulaR1C1 = "=COUNTIF(RC:RC,""=2"")" Range("H4").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C:RC,""=2"")" End Sub
i'm using office 2007
Any and all help is appreciated.
Two ways you could do this:
- As you say, a pivot table would be the obvious choice, but I'm not sure why you think that VBA would preclude you using one? Manipulating pivot tables in VBA is relatively easy once you get the hang of it - the macro recorder is really useful for learning the ins and outs of the object model, or here is a starter tutorial.
- Alternatively, you could connect to the data using ADO and use a SQL query to obtain the required information. Note that this would require the data to be suitably formatted on a separate worksheet, but this could be managed as part of the program. Microsoft have a good guide to using ADO to query Excel data.