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

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,

Who is Participating?
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
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.
wwviergAuthor Commented:
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  :-)

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.
wwviergAuthor Commented:
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

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.