Can't Reformat Range Because of "Run-time error '1004': Select method of Range class failed"!


I'm learning a painful lesson about how choosing to Select a range in Excel vba is sometimes a fatal error!

There seems to be no way around Selecting a range of cells in a column in order to:
Finde/Replace values in a range  via Ctrl+H-type VBA (from '-1' to 'N/A' for example)
Reformat a range from one type to another (text to currency)

I've read through many solutions to this venomous error message but none of them quite fit exactly what I need. What's maddening is that sometimes Select works fine - but sometimes it doesn't! My client cannot afford to have his customers get an error message!!!


Who is Participating?
StephenJRConnect With a Mentor Commented:
Range("A1:A10").Replace "-1", "N/A"

Open in new window

You rarely need to Select a range. Give more detail of what you are trying to do, better still post a workbook.
exactly as stephenJR mentioned no need to select a range.
Getting this error
Can't Reformat Range Because of "Run-time error '1004': Select method of Range class failed"!
means that Excel is trying to do something on a range but its focus is on an other sheet ! What you only need to do is to activate the sheet where the operation for the range is happeneing
Just before your range manipulation
do Sheets("Sheet1").activate (if Sheet1 is the sheet where the range manipulation exist)

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

monboisAuthor Commented:
Thanks for the code, StephenJR.

I admit I don't have a lot of experience with Excel VBA. Here's my code:

        Set oWorksheet = ThisWorkbook.Worksheets("Worksheet_Name")

Specifying and activating the worksheet came about from trying many different methods and even this is crapping out on me.

I'll give your code a shot and let you know if it works.

Thanks again.
monboisAuthor Commented:
StephenJR you are my savior! This works like magic! And so fast!

Quick follow-up questions:

1. While F8 walking through the VBA I'll sometimes get an 'out of range'-type error message even on commands that don't have a Select element and I'm wondering if this isn't just because I've waited too long to continue. This happens if I wait too long copying one range to another between 2 different spreadsheets.


2. How to I send Select a specific cell in the top of a spreadsheet where I'd like the user to start without actually using Select? I currently use:


But I'd love to eliminate all the Select statements if I can because they're a nightmare!

Let me know if I need to open separate questions for this.

Thanks so much!
Glad it worked. It depends on what you are doing, but for instance


can be abbreviated to


and for the Find and Replace you could do it all in one line

oWorksheet. oWorksheet.Range("Range_Name").Replace "-1", "N/A"

That way no sheet has to be activated. Selecting and activating slows down code too.
monboisAuthor Commented:
Thanks again, StephenJR. I'll try it.

Also, that copy/paste error message I get when stepping through the code is:

  Application-defined or Object-defined error

But again, I think it's just because I'm waiting too long to hit F8. Let me know if you think it's something else.

Thanks again!

Sorry monbois
but you mentioned about Activate which is the proposal I gave you. Did this help solve your issue or something else that you didn't mention ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.