Excel 200 VBA: getting 2 different Run-time Error '1004's when attempting same task

Posted on 2004-10-20
Last Modified: 2011-10-03
Hi All:

I'm going to give a lot of detail in hopes that it'll help get an answer back; sorry for long post.


I've got a bunch of cells in workbook #2 that have formulas that combine the values from cells within workbook # 2 *and* from corresponding cells in workbook #1.

I have a big chunk of VBA code that succesfully prompts the user for the name workbook #1, strips the path, calculates and "adjustment factor" (in some situations I have to "annualize" the combined data) and then does a search/replace of a dummy workbook name (i.e., abc.xls) as well as the adjustment factor place-holder imbedded in all the combining formulas.

Because abc.xls, the dummy workbook name, does not exist, I have leading apostrophes on all these formulas so Excel doesn't burp by prompting for abc.xls for every cell w/ one of these formulae.

I also have a sub procedure to remove these apostrophes which I've used  in numerous other situations:

Public Sub ApostroRemove()
    Dim currentcell As Range
    For Each currentcell In Selection
        If currentcell.HasFormula = False Then
            'Verifies that procedure does not change the
            'cell with the active formula so that it contains
            'only the value.
            currentcell.Formula = currentcell.Value
        End If
End Sub

I've narrowed down my errors to when my code is trying to remove the apostrophes - everything else works fine.

If I run this testing code:

Sub Test_FinalStep()
    ' and lastly, remove all leading apostrophes
    ' Application.Goto Sheets("05-06 Low Income").Range("D1:E76")
    ' ThisWorkbook.Worksheets("05-06 Low Income").Activate
    ' ActiveSheet.Range("D1:E76").Select
    Worksheets("05-06 Low Income").Range("D1:E76").Select
    ' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59")
    ' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate
    ' ActiveSheet.Range("D1:E59").Select
    Worksheets("05-06 Cost Limits").Range("D1:E59").Select
End Sub

I get my 1st error '1004':

"Select method of range class failed"

and Debug points me to the "Worksheets("05-06 Cost Limits").Range ("D1:E59").Select" line.

Note: The error points to this line *if* I run the code when the "Low Income sheets is active (visible). Also, all the apostorphes have been removed from the "Low Income" sheet formulas. If I run the above code w/ the "Cost Limits" sheet active (visible), the debug points me to the "Worksheets("05-06 Low Income").Range("D1:E76").Select" line and ...  no apostrophes havebeen removed from any formula!!

Looking in newsgroups & doing misc. googles, it was suggested that maybe something is not "active' that should be, so I've tried two different scenarios using the above code (after un-commenting the relevant lines), 1) using the "Application.GoTo" lines and 2) using the "ThisWorkbook" and "ActiveSheet" lines.

Each of these generated the same 2nd error '1004':

"Application-defined or object-defined error"

and debug pointed me to the "currentcell.Formula = currentcell.Value" line at the bottom of the For Each loop in the ApostroRemove proc.

Note: Both attempts (i.e., using each the "Application.GoTo" and the "ThisWorkbook/ActiveSheet" versions) resulted exactly the same.

It also didn't matter which sheet was active when I ran the code,

In all cases all apostrophes have been removed from the "Low Income" sheet formulas *and* also from the first cell that had an apostrophe on the "Cost Limits" sheet.


Thats about all I can think of to try at this point ....

Anybody: any ideas/advice?

Worse case, is that I completely do away w/ the dummy workbook name in all the imbedded formulas methodology, and after prompting the user for the name of workbook #1, populate all the cells directly w/ constructed string values.

I'd prefer, however, to figure this bugger out ...

Thanks in advance for any help/ideas,

Question by:wwvierg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2

Accepted Solution

sigmacon earned 500 total points
ID: 12366140
I think you were only one step away from the solution to your problem. I setup worksheets with the names given above and tested your code. I was able to reproduce the error, which is caused by the fact that you are trying to perform a range select on a worksheet that doesn't have the focus. I can go into more detail but here is the fix, using your code:

Sub Test_FinalStep()
    ThisWorkbook.Worksheets("05-06 Low Income").Activate
    Worksheets("05-06 Low Income").Range("D1:E76").Select
    ThisWorkbook.Worksheets("05-06 Cost Limits").Activate
    Worksheets("05-06 Cost Limits").Range("D1:E59").Select
End Sub

Expert Comment

ID: 12366146
OK, one more comment. Make sure you have 'activated' the worksheet before you are trying to perform any form of selection on it. I hope I could help. Good luck.

Author Comment

ID: 12366333
Hi Sigmacom:

Many thanks for giving it a whirl, but alas - this gen's the same 2nd error as I described oroignally ("Application-defined or object-defined error")

I.e., it bombs out with Debug pointing to the "currentcell.Formula = currentcell.Value" line in the ApostroRemove proc.

Also, as before, it does erase all apostrophes in "Low Income"s formulas, as well as the 1st occurence in "Cost Limits".

Again, thanks ... but no points mate  :-)


Expert Comment

ID: 12366512
I ran that stuff again, and did not get the 2nd error. That is to say the code in the version I proposed selects worksheets just fine and replaces text-formulas with the real formula and leaves other formulas untouched. So it may have something to do with the data itself ... ? Could you make some fake data producing the error available as comma-separated values, so I can test? Thanks.

Author Comment

ID: 12366885
Hi sigmacon:

Weird, reading your reply.

It's almost 11PM and a bottle of wine is nearly gone!!!

The reason I came back here was to offer a mea culpa ....

*Your* code ran bitchen.

*My* workbook DID have a bad formula (evident after manually removing the leading apostrophe) ... and guess where it was ..

Yup - the cell *after* the one cell that did resolve OK on the 2nd sheet when calling the ApostroRemove proc.

It's funny (or sad?) how after many days of putting this thing together, I transposed a !' as '! ..... in one cell out of some 400+ cells

Anyway, once I spotted that I figured I'd retry my original code, and it bombed again.

Went back & tried yours (where you passs a range to ApostroRemove vs. a selection) and it flew!!

I'm now a happpy, albeit exhausted, camper.

Thanks again for your help.

Case resolved: you have earned the offered points ... in spades.

Thanks again for  both your 1st post - and the persistence to try it again and reply once more.

After numemrous 12 hour days this week (and bottles of wine), I'm off to bed for a good sleep.

Warm Regards


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
draw a Christmas tree by using a nested loop? 26 92
add projects t working set in maven 2 41
Java pass by reference 3 16
MySQL programmer starter 25 23
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question