Application Defined or Object Defined Error

[SBF12345]

I have an application defined or object defined error on the following line:

SearchArray = Sheets("CurrentSheet").Range(Cells(6, 5), Cells(EndArray, 6))

EndArray is an integer variable and SearchArray is a Range variable. I don't see where the problem is occurring. Any ideas?

 

[Fluff]

Which sheet is active when you run the code?

 

[SBF12345]

Sheets("CurrentSheet")

 

[Fluff]

You're missing the word "Set" at the start of the line, but if "CurrentSheet" is not the active sheet then you will get another erro, as you haven't fully qualified the range.

 

[SBF12345]

wait a second, still receiving the error with a set introduced.

Set SearchArray = Sheets("CurrentSheet").Range(Cells(6, 5), Cells(EndArray, 6))

 

[shg]

As written, Cells still refers to the active worksheet.

  With Worksheet("CurrentSheet")
    Set SearchArray = .Range("E6", .Cells(EndArray, "F"))
  End With

 

[SBF12345]

The with statement is working. Thank you