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