Solved

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

Posted on 2004-10-20
5
314 Views
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.

Background:

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
    Next
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
    ApostroRemove
    ' 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
    ApostroRemove
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,

Out
0
Comment
Question by:wwvierg
  • 3
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
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
    ApostroRemove
    ThisWorkbook.Worksheets("05-06 Cost Limits").Activate
    Worksheets("05-06 Cost Limits").Range("D1:E59").Select
    ApostroRemove
End Sub
0
 
LVL 8

Expert Comment

by:sigmacon
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.
0
 

Author Comment

by:wwvierg
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  :-)

Out
0
 
LVL 8

Expert Comment

by:sigmacon
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.
0
 

Author Comment

by:wwvierg
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

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
allswap challenge 6 116
What is Python programming? 3 122
silent install of security banner via msiexec command 4 95
Generate Unique ID in VB.NET 21 62
This article will show, step by step, how to integrate R code into a R Sweave document
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

828 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