|
2 different Run-time Error '1004' ... HELP!!!!!
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 code that succesfully prompts the user for the name workbook #1 and then does a search/replace of a dummy workbook name (i.e., abc.xls) 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. 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 point 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 this newsgroup & 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, 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 inthe ApostroRemove proc. Note: Both attempts (i.e., using the Application.GoTo" and the "ThisWorkbook/ActiveSheet") 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 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 methodologt, 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 |
2 different Run-time Error '1004' ... HELP!!!!!
Hi Dave:
Many thanks for such a quick reply - just finished dinner, sippin' a nice wine, & sat down at the home computer and and voila' ... there's your reply. Alas - tried both and both (your 2nd first, then your "select" version) and both gen'd the same 2nd error as I originally described. Debug still points to "currentcell.Formula = currentcell.Value" yet all apostrophes in the "Low Income" sheet have been removed, yet only yhe 1st cell on the "Cost Limits" sheet. FWIW, your first suggestion was very similar to what I've read in similar posts/threads today on a variety of sites, so really thought that you had nailed it. What can I say but ... Que sera, sera! Again, many thanks for giving it a go. Looking like I'll have to go w/ brute force, i.e., populated each cell individually by constructing relevant strings for each formula. Ciao' for now In article , says... You can only select a range on a worksheet's that selected: 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 with Worksheets("05-06 Low Income") .select .Range("D1:E76").Select end with ApostroRemove ' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59") ' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate ' ActiveSheet.Range("D1:E59").Select with Worksheets("05-06 Cost Limits") .select .Range("D1:E59").Select end with ApostroRemove End Sub But you could remove all the .selects and do something like: Sub Test_FinalStep() call ApostroRemove(Worksheets("05-06 Low Income").range("D1:E76")) call apostroremove(Worksheets("05-06 Cost Limits").Range("D1:E59")) End Sub Public Sub ApostroRemove(rng as range) Dim currentcell As Range For Each currentcell In rng 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 List Lurker wrote: 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 code that succesfully prompts the user for the name workbook #1 and then does a search/replace of a dummy workbook name (i.e., abc.xls) 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. 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 point 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 this newsgroup & 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, 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 inthe ApostroRemove proc. Note: Both attempts (i.e., using the Application.GoTo" and the "ThisWorkbook/ActiveSheet") 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 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 methodologt, 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 |
2 different Run-time Error '1004' ... HELP!!!!!
Hi Dave:
It's almost 11PM and the bottle of wine is nearly gone!!! The reason I'm posting once more (before I crash) is to offer a mea culpa .... *Your* code ran bitchen. *My* workbook had 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 BOOM it still didn't work!!! (baastaaard) Went back & tried yours (where you passs a range to ApostroRemove vs. a selection) and used your CALL ApostroRemove and it flew!! I'm now a happpy, albeit exhausted, camper. Thanks again for your help. Kudos also to the newsgroup gods - what a resource this is. Out In article , wwvlists- says... Hi Dave: Many thanks for such a quick reply - just finished dinner, sippin' a nice wine, & sat down at the home computer and and voila' ... there's your reply. Alas - tried both and both (your 2nd first, then your "select" version) and both gen'd the same 2nd error as I originally described. Debug still points to "currentcell.Formula = currentcell.Value" yet all apostrophes in the "Low Income" sheet have been removed, yet only yhe 1st cell on the "Cost Limits" sheet. FWIW, your first suggestion was very similar to what I've read in similar posts/threads today on a variety of sites, so really thought that you had nailed it. What can I say but ... Que sera, sera! Again, many thanks for giving it a go. Looking like I'll have to go w/ brute force, i.e., populated each cell individually by constructing relevant strings for each formula. Ciao' for now In article , says... You can only select a range on a worksheet's that selected: 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 with Worksheets("05-06 Low Income") .select .Range("D1:E76").Select end with ApostroRemove ' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59") ' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate ' ActiveSheet.Range("D1:E59").Select with Worksheets("05-06 Cost Limits") .select .Range("D1:E59").Select end with ApostroRemove End Sub But you could remove all the .selects and do something like: Sub Test_FinalStep() call ApostroRemove(Worksheets("05-06 Low Income").range("D1:E76")) call apostroremove(Worksheets("05-06 Cost Limits").Range("D1:E59")) End Sub Public Sub ApostroRemove(rng as range) Dim currentcell As Range For Each currentcell In rng 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 List Lurker wrote: 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 code that succesfully prompts the user for the name workbook #1 and then does a search/replace of a dummy workbook name (i.e., abc.xls) 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. 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 point 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 this newsgroup & 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, 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 inthe ApostroRemove proc. Note: Both attempts (i.e., using the Application.GoTo" and the "ThisWorkbook/ActiveSheet") 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 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 methodologt, 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 |
2 different Run-time Error '1004' ... HELP!!!!!
Glad you got it working.
In vino, veritas. (Doesn't quite fit, but if you're still drinking, that shouldn't matter <vvbg.) List Lurker wrote: Hi Dave: It's almost 11PM and the bottle of wine is nearly gone!!! The reason I'm posting once more (before I crash) is to offer a mea culpa .... *Your* code ran bitchen. *My* workbook had 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 BOOM it still didn't work!!! (baastaaard) Went back & tried yours (where you passs a range to ApostroRemove vs. a selection) and used your CALL ApostroRemove and it flew!! I'm now a happpy, albeit exhausted, camper. Thanks again for your help. Kudos also to the newsgroup gods - what a resource this is. Out In article , wwvlists- says... Hi Dave: Many thanks for such a quick reply - just finished dinner, sippin' a nice wine, & sat down at the home computer and and voila' ... there's your reply. Alas - tried both and both (your 2nd first, then your "select" version) and both gen'd the same 2nd error as I originally described. Debug still points to "currentcell.Formula = currentcell.Value" yet all apostrophes in the "Low Income" sheet have been removed, yet only yhe 1st cell on the "Cost Limits" sheet. FWIW, your first suggestion was very similar to what I've read in similar posts/threads today on a variety of sites, so really thought that you had nailed it. What can I say but ... Que sera, sera! Again, many thanks for giving it a go. Looking like I'll have to go w/ brute force, i.e., populated each cell individually by constructing relevant strings for each formula. Ciao' for now In article , says... You can only select a range on a worksheet's that selected: 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 with Worksheets("05-06 Low Income") .select .Range("D1:E76").Select end with ApostroRemove ' Application.Goto Sheets("05-06 Cost Limits").Range("D1:E59") ' ThisWorkbook.Worksheets("05-06 Cost Limits").Activate ' ActiveSheet.Range("D1:E59").Select with Worksheets("05-06 Cost Limits") .select .Range("D1:E59").Select end with ApostroRemove End Sub But you could remove all the .selects and do something like: Sub Test_FinalStep() call ApostroRemove(Worksheets("05-06 Low Income").range("D1:E76")) call apostroremove(Worksheets("05-06 Cost Limits").Range("D1:E59")) End Sub Public Sub ApostroRemove(rng as range) Dim currentcell As Range For Each currentcell In rng 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 List Lurker wrote: 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 code that succesfully prompts the user for the name workbook #1 and then does a search/replace of a dummy workbook name (i.e., abc.xls) 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. 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 point 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 this newsgroup & 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, 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 inthe ApostroRemove proc. Note: Both attempts (i.e., using the Application.GoTo" and the "ThisWorkbook/ActiveSheet") 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 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 methodologt, 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 -- Dave Peterson |
| All times are GMT +1. The time now is 02:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com