Solved

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

Posted on 2004-10-20
5
303 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now