2

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
Community
  • 1
  • 1
mike_326
  • 25
  • 1
  • 5
  • 2
    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 Dec 08 '15 at 14:42
  • 1
    Not sure why you are getting that but FYI the .selects can be problematic. Look here to see how to avoid using them. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – MatthewD Dec 08 '15 at 14:42
  • 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 Dec 08 '15 at 15:01
  • Thanks everyone, I used set myOCC = selection – mike_326 Dec 09 '15 at 16:36

1 Answers1

3

rather then:

Selection = myOCC

use:

Set myOCC = Selection

etc.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99