error 1004, method of class/object failed

Originally Posted by daboyroy

Hi there,

I keep on running into some errors with vba excel. The goal is to copy some columns and delete some rows in a large file depending on the values of certain cells in the file. (I have the file open while trying to do so.) 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!

Roy

Your problem is that you are referencing an incorrect point, it appears that your 'xlbook' is not correctly assigned, then the Sheet1 is not assigned, then the Cell range is not assigned, thus the '=D2/256' goes into the (previously) 'active' cell instead of U2.

(by 'assigned', I mean 'activated/selected' etc)

I hope this helps you track the error, I may find more details but the 'Select' / 'Activate' and when/why to do those is a little ahead of my current learning.

Added:

With

Sub ttyu()
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Set xlBook = Workbooks.Add
Set xlSheet = Worksheets("Sheet1")
xlSheet.Activate
Dim i
Dim CurrentCell
Dim OldComp
Dim NewComp
With xlSheet
.Range("U1").Select
ActiveCell.Formula = "Latency"
Application.CutCopyMode = False
.Range("U2:U50000").Formula = "=D2/256"
xlSheet.Range("V1").Select
ActiveCell.FormulaR1C1 = "Type"
.Range("V2:V50000").Formula = "target"
.Columns("T:T").Copy
' note, there is nothing in T

.Range("W:W").Select
ActiveSheet.Paste
Application.CutCopyMode = False
MsgBox "to here"
OldComp = 0
For i = 0 To 50000
CurrentCell = "W" & i + 2
.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 With
End Sub

I can achieve what you appear to be doing, but after the msgbox I do not understand what you are trying to do.

What was 'target' put into column V ?

hth
---
---