Run time error 1004

Run time error 1004

• saisiri

  I also have a problem with the same run-time error. My macro copy and pastes information. It works fine when run as a macro, but when I assign it to a comand button I get the run-time error 1004.

  
  

• Gurpreet Singh Gill

  Thanks a lot Andy!

  It works with your suggestion. I changed them both from 0 to 1.

  However, why couldn't Col be zero

  
  

• HopeDreamsComeTrue

  Hey guys.

  I have ran into a wall with this portion of the VBA code. This macro was orignally written for 2003 excel, however, since the 2007 increased it's pivot table limits, I wanted to take full advantage of this.

  All I did was increase the range and everything else should be the same, however, I got a run time error 1004 telling me that

  "A field in your source data has more unique items than can be used in a PivotTable report. Microsoft Office Excel may not be able to create the report....."

  here is the code snippit.

  If CurrRow > 250000 + 18 Then
  IntPress = MsgBox("Too many unique records for Pivot table; only 250000 allowed")
  Exit Sub
  End If
  Set BaseRange = ActiveWorkbook.Worksheets(SheetNeeded).Range("a18:i150000")
  BaseString = "'[" & BaseBook.Name & "]" & BaseRange.Worksheet.Name & "'!" & BaseRange.AddressLocal(ReferenceStyle:=xlR1C1)
  
  Windows(CurrBook).Activate
  #If xlver > 10 Then
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
  Array(Array(BaseString, "Basecase") _
  , Array(CompString, "ChangedCase"))). _
  CreatePivotTable TableDestination:="", TableName:="PivotTable3"
  
  #Else
  ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
  Array(Array(BaseString, "Basecase") _
  , Array(CompString, "ChangedCase"))). _
  CreatePivotTable TableDestination:="", TableName:="PivotTable3", _
  DefaultVersion:=xlPivotTableVersion10
  #End If

  The problem is where the #Else statement starts. That is the only area that is given me that error 1004.

  Any help would be appreciate it

  PS. I would assume that if I change the range it should accomondate for the increase of pivot items in 2007 excel.

  
  

• Mr Cai

  ADG is correct... you can view this thread at http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=337016&SiteID=1

  -brenda (ISV Buddy Team)

  
  
  

• vsnetdeveloper

  Hi All,

  First off, my computer knowledge is very limited so forgive me if I sound redudant. I'm having the exact same problem when I tried to import an excel file into Access, the following message appears: Run-time error '1004', Delete method of Range Class failed. I've been reading all of these threads and it just makes no sense to me as to how I would fix this problem. I was told that the security level on my excel spreadsheet needs to be on low, which it already is, and I still get the exact same error. Please help!!! (the error is highlighted in yellow).

  'Deleting excess rows due to sub totaling of status groups
  Rows("1:1").Select
  Selection.Insert Shift:=xlDown
  Selection.AutoFilter
  Selection.AutoFilter Field:=1, Criteria1:="Type Total"
  Range("A65536").Select
  Selection.End(xlUp).Select
  Range(Selection, Selection.End(xlUp)).Select
  If ActiveCell.Row = 1 Then
  Range("A65536").Select
  Selection.End(xlUp).Select
  ActiveCell.EntireRow.Select
  End If
  Selection.Delete Shift:=xlUp
  Selection.AutoFilter Field:=1, Criteria1:="="
  Range("D65536").Select
  Selection.End(xlUp).Select
  Range(Selection, Selection.End(xlUp)).Select
  If ActiveCell.Row = 1 Then
  Range("D65536").Select
  Selection.End(xlUp).Select
  ActiveCell.EntireRow.Select
  End If

  Selection.Delete Shift:=xlUp
  Selection.AutoFilter Field:=1
  Selection.AutoFilter
  Rows("1:1").Delete

  'Deleting remaining rows
  Columns("J:L").Select
  Selection.Delete Shift:=xlToLeft
  Columns("E:E").Select
  Selection.Delete Shift:=xlToLeft

  'Done
  Range("A1").Select

  '// Deletes the macro from the workbook
  Dim xDelMacro As Object
  Set xDelMacro = Application.VBE.ActiveVBProject.VBComponents
  xDelMacro.Remove VBComponent:=xDelMacro.Item("DMOS_Export_Prep")

  
  End Sub

  
  
  

• Mikaelm

  At a guess I would think Autofilter is still on when you try and delete the selection.
  I not really sure what your code is trying to do but maybe add a line to clear any filtering before deletion.
  
  
  

• sheldono

  I found the error was caused by local range references. You need to replace

  Cells(...) --> Worksheets( ).Cells(...) ' replace ' ' and '...' yourself.

  Range(...) --> worksheets( ).Range(...)

  etc

  
  

• Swapna.B.

  The variable Col will have a value of zero, which will cause an error.
  
  Also you appear to be swapping row and col. The Cells object takes a Row then a Column argument.
  
  ThisWorkbook.Sheets("Random Function").Cells(Row + 3 , Col) =
  
  
  

• Tom_Liu

  For the following code, I also got Run-Time error 1004. What is the problem with this code

  Sub CommandButton1_Click()
  '===============================================
  'Rand - Return a random number in a given range.
  '
  'Parameters:
  ' Low - The lower bounds of the range.
  ' High - The upper bounds of the range.
  '
  'Returns:
  ' Returns a random number from Low..High.
  '===============================================
  'Public Function Rand(ByVal Low As Long, _
  ByVal High As Long) As Long
  ' Rand = Int((High - Low + 1) * Rnd) + Low
  'End Function
  Dim Col As Long
  Dim Row As Long

  For Col = 0 To 100
  For Row = 0 To 100
  ThisWorkbook.Sheets("Random Function").Cells(Col, Row + 3) = Int((2 - (-2) + 1) * Rnd) + (-2)
  Next Row
  Next Col

  End Sub

  
  

• Milad.a.p

  Because Cells(1,1) is equivalent to range("A1")
  
  Therefore Cells(0,1) would be 1 row above, which does not exist.
  
  
  

• Soteriologist

  I posted a similar thread recently. There are a few helpful references provided by Brenda in her reply. I found that to cure the problems I had, I need to fully qualify the reference to the ranges.

  In the end I created an Excel,sheet variable to hold the reference to the page I wanted to work with. In theory this was not necessary, in practice it was! I mainly work with Office 97 (my clients choice!).

  Hope this helps

  
  
• Run time error 1004