Excel 200 VBA: getting 2 different Run-time Error '1004's when attempting same task
Posted on 2004-10-20
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
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:
' and lastly, remove all leading apostrophes
' Application.Goto Sheets("05-06 Low Income").Range("D1:E76")
' ThisWorkbook.Worksheets("05-06 Low Income").Activate
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
Worksheets("05-06 Cost Limits").Range("D1:E59").Select
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,