Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1516to1520
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Pivot Table Fehler 1004

Pivot Table Fehler 1004
26.09.2016 15:37:25
Jens
Liebe Coomunity,
ich verzweifle an meinem Pivot Code:(. Folgender Teil des Codes produziert Runtime error 1004:
With pvt8
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
Ich habe für die Pivottables dadrüber genau den gleichen Code verwendet und sie werden einwandfrei geschrieben. Ich habe alles probiert und komme nicht dahinter, was der Grund für den Error ist. Das xls habe ich beigefügt. Danke für eure Hilfe!
Datei:
https://www.herber.de/bbs/user/108417.xls
Code:
Gesamter Code:
End With
I just copied the line from the pivot aboe and adjusted it, there it works perfectly. Do anyone has an idea why this error is occuring. I tried everything from rewriting, restructuring the table, restructuring the code, nothing works and I can't explain why. Thanks for your help!
Code:
Sub pivot()
'''''''''''K Variables
Dim loopcountermk As Long
Dim Modulendk As Long
Dim Modulstak As Long
Dim Modulnumk As Long
Modulstak = 27
Modulnumk = 8
Modulendk = Modulstak + Modulnumk
Modulendk = Modulendk - 1
'''''''''''I Variables
Dim loopcountermi As Long
Dim Modulendi As Long
Dim Modulstai As Long
Dim Modulnumi As Long
Modulstai = 36
Modulnumi = 8
Modulendi = Modulstai + Modulnumi
Modulendi = Modulendi - 1
'''''''''t3 variables
Dim Startto As Long
Dim loopcounterto As Long
Dim Modulendto As Long
Dim Modulstato As Long
Dim Modulnumto As Long
Modulstato = 27
Modulnumto = 8
Modulendto = Modulstato + Modulnumto
Modulendto = Modulendto - 1
''''t3 variables count
Dim loopcountertoc As Long
Dim modulstatoc As Long
Dim modulnumtoc As Long
Dim modulentoc As Long
ReDim modultoc(1 To Modulnumk)
modulstatoc = 44
modulnumtoc = 3
modulendtoc = modulstatoc + modulnumtoc
modulendtoc = modulendtoc - 1
''''''''b3 variables count
Dim loopcounterboc As Long
Dim modulstaboc As Long
Dim modulnumboc As Long
Dim modulenboc As Long
ReDim modulboc(1 To Modulnumk)
modulstaboc = 48
modulnumboc = 3
modulendboc = modulstaboc + modulnumboc
modulendboc = modulendboc - 1
'''''''''''o variables
Dim loopcountero As Long
Dim Modulendo As Long
Dim Modulstao As Long
Dim Modulnumo As Long
Modulstao = 53
Modulnumo = 4
Modulendo = Modulstao + Modulnumo
Modulendo = Modulendo - 1
'' K Pivot
Dim pvc As PivotCache
Dim pvt As PivotTable
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim pvt2 As PivotTable
Dim pvt8 As PivotTable
Set ws1 = Worksheets("Numbers")
Set ws2 = Worksheets("Sheet2")
Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A3").CurrentRegion)
Set pvt = pvc.CreatePivotTable(TableDestination:=ws2.Range("A3"))
For loopcounterto = Modulstato To Modulendto
With pvt
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
Next loopcounterto
With pvt.DataPivotField
.Orientation = xlRowField
.Position = 1
End With
'' I pivot
Sheets("Numbers").Activate
Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A3").CurrentRegion)
Set pvt1 = pvc.CreatePivotTable(TableDestination:=ws2.Range("A16"))
For loopcounterto = Modulstai To Modulendi
With pvt1
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
Next loopcounterto
With pvt1.DataPivotField
.Orientation = xlRowField
.Position = 1
End With
'' O pivot
Sheets("Numbers").Activate
Set pvc = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=ws1.Range("A3").CurrentRegion)
Set pvt8 = pvc.CreatePivotTable(TableDestination:=ws2.Range("A61"))
For loopcounterto = Modulstao To Modulendo
With pvt8
.AddDataField Field:=.PivotFields(ws1.Cells(2, loopcounterto).Value), _
Caption:="Average" & ws1.Cells(2, loopcounterto).Value, _
Function:=xlAverage
End With
Next loopcounterto
With pvt8.DataPivotField
.Orientation = xlRowField
.Position = 1
End With
'' 1First 3
Sheets("Numbers").Activate
Set pvt2 = pvc1.CreatePivotTable(TableDestination:=ws2.Range("A30"))
With pvt2
.PivotFields(ws1.Cells(2, modulstatoc).Value).Orientation = xlRowField
End With
With pvt2
.PivotFields(ws1.Cells(2, modulstatoc).Value).Orientation = xlDataField
End With
'' 2First 3
Sheets("Numbers").Activate
Set pvt3 = pvc1.CreatePivotTable(TableDestination:=ws2.Range("C30"))
With pvt3
.PivotFields(ws1.Cells(2, modulstatoc + 1).Value).Orientation = xlRowField
End With
With pvt3
.PivotFields(ws1.Cells(2, modulstatoc + 1).Value).Orientation = xlDataField
End With
'' 3First 3
Sheets("Numbers").Activate
Set pvt4 = pvc1.CreatePivotTable(TableDestination:=ws2.Range("E30"))
With pvt4
.PivotFields(ws1.Cells(2, modulendtoc).Value).Orientation = xlRowField
End With
With pvt4
.PivotFields(ws1.Cells(2, modulendtoc).Value).Orientation = xlDataField
End With
'' Bot 3
Sheets("Numbers").Activate
Set pvt5 = pvc1.CreatePivotTable(TableDestination:=ws2.Range("A46"))
With pvt5
.PivotFields(ws1.Cells(2, modulstaboc).Value).Orientation = xlRowField
End With
With pvt5
.PivotFields(ws1.Cells(2, modulstaboc).Value).Orientation = xlDataField
End With
'' 2Bottom 3
Sheets("Numbers").Activate
Set pvt6 = pvc1.CreatePivotTable(TableDestination:=ws2.Range("C46"))
With pvt6
.PivotFields(ws1.Cells(2, modulstaboc + 1).Value).Orientation = xlRowField
End With
With pvt6
.PivotFields(ws1.Cells(2, modulstaboc + 1).Value).Orientation = xlDataField
End With
'' 3bottom 3
Sheets("Numbers").Activate
Set pvt7 = pvc1.CreatePivotTable(TableDestination:=ws2.Range("E46"))
With pvt7
.PivotFields(ws1.Cells(2, modulendboc).Value).Orientation = xlRowField
End With
With pvt7
.PivotFields(ws1.Cells(2, modulendboc).Value).Orientation = xlDataField
End With
pvt.DataPivotField.Caption = "Knowledge"
pvt1.DataPivotField.Caption = "Impact"
pvt2.CompactLayoutRowHeader = "Top3 Ranking1"
pvt3.CompactLayoutRowHeader = "Top3 Ranking2"
pvt4.CompactLayoutRowHeader = "Top3 Ranking3"
End Sub

1
Beitrag zum Forumthread
Beitrag zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Pivot Table Fehler 1004
26.09.2016 16:29:45
Luschi
Hallo Jens,
Der PivotCache-Bereich für Deine Pivottabellen umfaßt den Bereich :
ws1.Range("A3").CurrentRegion)
also: Numbers!$A$2:$AZ$22
Für pvt8 beginnt die Auswertung aber bei: ws1.Cells(2, loopcounterto).Value
'loopcounterto' hat den Anfangswert Modulstao (=53)
und das ist Zelle: Numbers!$BA$2
und liegt damit außerhalb des PivotCache-Bereiches.
Gruß von Luschi
aus klein-Paris
PS: Schreibe mal iun die allererste Zeile von 'Modul2' fir Zeile: Option Explicit
und kompiliere dann mal. Da wirst Du feststellen, daß einige Variablen nicht definiert sind (pvt2 bis pvt8) und einige Variablen fals deklariert sind (zB. modulenboc statt modulendboc)
Anzeige

74 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige