Run time error 1004:Application defined or object defined error on range object

Question

Dim collet As String
collet = ThisWorkbook.ColLetter(ColCount) + ":" + LTrim(Str(Target.Row))
Set my_r = Target(collet).Select

I'm getting the runtime error at the last line in my code.Cannot figure out why

Answer 1

It would help to know exactly what you're trying to accomplish here, but I can make a few suggestions.

• First, when referencing a single cell as I believe you are trying to do, a colon is not required. So collet should contain something like "B2", not "B:2".

• Second, when you call Select on a Range object it uses the parameter as an offset, not an absolute reference. So if Target is cell B2, and collect is B2, then Target("B2").Select will actually select cell C3.

• And lastly, I'm assuming by the presence of the Target object that this code is inside an event handler. Make sure that by selecting a different range you are not triggering the same event again. You could end up with an infinite loop that ends only when you reach the bottom or the end of your spreadsheet. This scenario can cause the runtime error 1004 that you are seeing.