Application defined or object defined error 1004

Question

I am trying define two ranges (myADR & myOCC) to use in a Linest Formula. However I keep getting this 1004 error. I've tried two ways, the second way is commented out. Does anyone know how to fix this?

Sub LinestFormula()

Dim nCols As Integer
Dim myOCC As Range
Dim myADR As Range
Dim nRows3 As Integer

Range("A1").CurrentRegion.Select
nCols = Selection.Columns.Count

ActiveCell.Offset(5, 1).Resize(1, nCols - 2).Select
Selection.Copy
Range("A1").Select
Selection.End(xlToRight).Offset(0, 2).Select
ActiveCell = "OCC"
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues, Transpose:=True
nRows3 = Selection.Rows.Count
'Selection = myOCC


Cells(5, 2).Select
Selection.Resize(1, nCols - 2).Select
Selection.Copy
Range("A1").Select
Selection.End(xlToRight).Offset(0, 3).Select
ActiveCell = "ADR"
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues, Transpose:=True

Range("A1").End(xlToRight).Offset(1, 2).Resize(nRows3, 1).Select
Selection = myOCC
Range("A1").End(xlToRight).Offset(1, 3).Resize(nRows3, 1).Select
Selection = myADR

I'd try to avoid using .Select and ActiveCell, and do those actions on the same line if possible. It will be much easier to read and understand, and might be easier to spot the problem. Which line is the code failing on ? — Vulthil
Not sure why you are getting that but FYI the .selects can be problematic. Look here to see how to avoid using them. stackoverflow.com/questions/10714251/… — MatthewD
I would also like to know how you can possibly know what is the activecell when this sub is called, so how can you know what you will be working on. — Bob Phillips

Gary's Student Gary's Student · Accepted Answer · 2015-12-08T14:52:37

3

votes

rather then:

Selection = myOCC

use:

Set myOCC = Selection

etc.

Application defined or object defined error 1004

1 Answers