error 1004, method of class/object failed

[daboyroy]

Hi there,

I keep on running into some errors with vba excel. The goal is to delete some rows in a large file depending on the values of certain cells in the file. Heard it may have something to do with vba creating its own objects so I hardcoded that, to no avail.

Option Explicit

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")

Dim i
Dim CurrentCell
Dim OldComp
Dim NewComp

xlSheet.Range("U1").Select
ActiveCell.FormulaR1C1 = "Latency"
xlSheet.Range("U2").Select
ActiveCell.FormulaR1C1 = "=RC[-18]/256"
xlSheet.Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U50000"), Type:=xlFillDefault

HERE (LAST LINE) AN ERROR OCCURS: AUTOFILL METHOD OF RANGE CLASS FAILED. OCCURS ALSO IF I PUT xlSheet IN FRONT OF 'RANGE' IN THIS LAST LINE. ERROR DISAPPEARS WHEN I GED RID OF xlSheet IN THE LINE BEFORE THAT

xlSheet.Range("V1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("V2").Select
ActiveCell.FormulaR1C1 = "target"
Selection.AutoFill Destination:=Range("V2:V50000"), Type:=xlFillDefault
xlSheet.Columns("T:T").Select
Selection.Copy
xlSheet.Columns("W:W").Select
ActiveSheet.Paste

OldComp = 0
For i = 0 To 50000
CurrentCell = "W" & i + 2
xlSheet.Range("CurrentCell").Activate

HERE AN ERROR OCCURS: METHOD RANGE OF OBJECT _WORKSHEET FAILED. IF I LOSE THE xlSheet PART, i GET: METHOD RANGE OF OBJECT _GLOBAL FAILED.


NewComp = ActiveCell.Value
If NewComp = OldComp Then
xlSheet.Rows(i + 2).Delete
End If

OldComp = xlSheet.Range("CurrentCell").Value
Next 'i
End Sub

Thanks for any help!

 

[Gustav]

Your code will work after the following changes:

1. Use
xlApp.Selection.AutoFill Destination:=xlSheet.Range("U2:U50000"), Type:=xlFillDefault
instead of your
Selection.AutoFill Destination:=Range("U2:U50000"), Type:=xlFillDefault


2. Use
xlSheet.Range(CurrentCell).Activate
instead of your
xlSheet.Range("CurrentCell").Activate


3. Use
OldComp = xlSheet.Range(CurrentCell).Value
instead of your
OldComp = xlSheet.Range("CurrentCell").Value