William Viergever
asked on
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.
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("0 5-06 Low Income").Activate
' ActiveSheet.Range("D1:E76" ).Select
Worksheets("05-06 Low Income").Range("D1:E76").S elect
ApostroRemove
' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59")
' ThisWorkbook.Worksheets("0 5-06 Cost Limits").Activate
' ActiveSheet.Range("D1:E59" ).Select
Worksheets("05-06 Cost Limits").Range("D1:E59").S elect
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").S elect" 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
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("0
' ActiveSheet.Range("D1:E76"
Worksheets("05-06 Low Income").Range("D1:E76").S
ApostroRemove
' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59")
' ThisWorkbook.Worksheets("0
' ActiveSheet.Range("D1:E59"
Worksheets("05-06 Cost Limits").Range("D1:E59").S
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").S
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"
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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
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
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.
ASKER
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
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